Prev: How to Create a Parameterised GROUP BY Without Using Dynamic-SQL
Next: Compare Data In Two Tables
From: Erland Sommarskog on 2 Jul 2008 18:48
Bassam (egbas(a)yahoo.com) writes:
> The solution he offers thru sp_executesql is very complicated in many
> situations , like , if we have 10 parameter options for the user to
> choose from in the client , its not just ' = ' that the user can select
> for the parameter , it can be > , < , = , Like , Not Like , Between ,
> Not Between , etc. If I prepare a possible route in the stored procedure
> for all the possible 10 parameters available to the user for 1 report ,
> then I have a nightmare procedure to build !
>
>
> An alternative is to carefully build the dynamic WHERE part in the
> client to avoid injection eliminating apostrophes or -- , I already
> built that complex procedure with vb.net in the client to produce the
> WHERE , then I used it over many complex reports that depend on dynamic
> stored procedures

I can see two main advantages between building in the client and in an SP:

1) A client language is better at string manipulation.
2) You don't have have umpteend parameters, not for the values, but
also the operands.

But else there is no major difference. You should produce a parameterised
statement, no matter you do it in the client or in the server. And you
should definitely not waste your time on eliminating apostrophes or --,
what if the user wants to search for that?

Dealing with the parameter list is no big deal at all. One thing to
keep in mind is that the parameter list to the dynamic SQL you generate
can be the same regardless of what the user picks. If you are client-
side, it's fairly simple to add only those you need, but in T-SQL, use
a fixed parameter list. Then again, you can write a stored procedure
in C# or VB .Net to build your dynamic SQL.

In any case, it is my opinion, that either you have all your SQL in the
client, or you use stored procedures throughout. Once you have embraced
stored procedures, there is a separation of duties which says where the
SQL syntax should be take care of. What if you have lots of code in
the client that builds WHERE clauses, and then someone changes the base
query in the stored procedure?

> SQL Injection issue
> Build the dynamic WHERE carefully to avoid it , till MS can offer a very
> simple solution like
> Begin Single Statement
> END Single Statement
>
> then this injection will produce an error saying "Multiple statements are
> not allowed"
> Begin Single Statement
> SELECT * FROM dbo.Orders WHERE 1 = 1 AND ShipName LIKE '' DROP TABLE
> orders --'
> END Single Statement

But that is not going to help users to find their old mate O'Brien.

> Query-Plan Reuse issue
> how long it takes to create a plan ? one second ? that's not an issue in
> our case where about 200 users are accessing SQL Server at a given time
> and certainly not them all are using that report at the same moment ,
> maybe its an issue with 2000 user ?

How long time it takes to produce a plan depends on how complex the query
is. But for dynamic searches, the issue is not only about compilation as
such. A possibly bigger problem is that the cache gets littered with
plans that are rarely if ever reused. As long as you don't have variable
operators, you can do dynamic searches with static SQL using things like:

WHERE (col = @col OR @col IS NULL)
AND ...
OPTION (RECOMPILE)

This rocks in SQL 2008, where OPTION (RECOMPILE) makes SQL Server to
consider the full consequences of the current variable values. So this
will result in a compilation each time - but there will be no litter
in the cache. (In SQL 2005 OPTION (RECOMPILE) does not work so well.)

But if your searches has a complex basic query to them, it is not unlikely
that using a cached plan for common searches, is going to give your users
a better experience. That is, it can well take a second to compile a
query.

> it can be also a simple solution from MS by offering Forced
> parameterization

In my opinion, force parameterisation is a cover-up for poorly designed
applications. Keep also in mind that with forced parameterisation,
constants that really constants in the query also get parameterised, which
may be disadvantageous for performance.


> Permission Issue
> what's the problem of giving permission to users to table level ?

That depends on your business. In some shops this is completely out of
the question, and in other shops no one would care.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Michael Coles on 2 Jul 2008 20:26
"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9AD0A7A341DFYazorman(a)127.0.0.1...
> I can see two main advantages between building in the client and in an SP:
>
> 1) A client language is better at string manipulation.
> 2) You don't have have umpteend parameters, not for the values, but
> also the operands.

One more for the list of Erland's advantages - you can use an OO language
like C# to create classes that abstract away all the SQL code generation so
that your developers don't have to futz with the SQL directly. They could
simply pass in some parameters to a class method and execute the query
string returned; or with a little more development you could return a
properly parameterized SqlCommand object directly. This gives you the
advantage of centralizing your SQL code generation as well, so you can
ensure that all the SQL queries generated follow whatever your
organizational standards and best practices dictate (e.g., quoted
identifiers, etc.)

--

========
Michael Coles
"Pro SQL Server 2008 XML"
http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/



From: Dan Guzman on 3 Jul 2008 08:33
> One more for the list of Erland's advantages - you can use an OO language
> like C# to create classes that abstract away all the SQL code generation
> so that your developers don't have to futz with the SQL directly. They
> could simply pass in some parameters to a class method and execute the
> query string returned; or with a little more development you could return
> a properly parameterized SqlCommand object directly. This gives you the
> advantage of centralizing your SQL code generation as well, so you can
> ensure that all the SQL queries generated follow whatever your
> organizational standards and best practices dictate (e.g., quoted
> identifiers, etc.)

I agree that a client-side technique such as this is more robust than the
stored procedure alternative. I think one of Bassam's issues is that this
complicates deployment; instead of just changing a single proc, the client
method requires that the code be rebuilt and deployed to all clients. OTOH,
a change to the proc interface will probably require code changes anyway, at
least to take advantage of new functionality.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Michael Coles" <michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net> wrote in
message news:u60fqNK3IHA.2524(a)TK2MSFTNGP04.phx.gbl...
> "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
> news:Xns9AD0A7A341DFYazorman(a)127.0.0.1...
>> I can see two main advantages between building in the client and in an
>> SP:
>>
>> 1) A client language is better at string manipulation.
>> 2) You don't have have umpteend parameters, not for the values, but
>> also the operands.
>
> One more for the list of Erland's advantages - you can use an OO language
> like C# to create classes that abstract away all the SQL code generation
> so that your developers don't have to futz with the SQL directly. They
> could simply pass in some parameters to a class method and execute the
> query string returned; or with a little more development you could return
> a properly parameterized SqlCommand object directly. This gives you the
> advantage of centralizing your SQL code generation as well, so you can
> ensure that all the SQL queries generated follow whatever your
> organizational standards and best practices dictate (e.g., quoted
> identifiers, etc.)
>
> --
>
> ========
> Michael Coles
> "Pro SQL Server 2008 XML"
> http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/
>
>
>

From: TheSQLGuru on 3 Jul 2008 10:27
> instead of just changing a single proc, the client method requires that
> the code be rebuilt and deployed to all clients.

Hmm, sounds just like what MS is giving us with LINQ to SQL! :-)

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


 | 
Pages: 1
Prev: How to Create a Parameterised GROUP BY Without Using Dynamic-SQL
Next: Compare Data In Two Tables