Building and executing SQL Query dynamically, best practices

[Guest]

Hello,

I am developing ASP.NET 1.1 application against SQL Server 2000 db, where
users have ability to construct their own select criteria (for a where
clause). I already developed a user control where user can select field name,
operator (equals, like, begins with, etc) and value of the search criteria.
This control can already build where clause part. Now I am at the point,
where I need to execute SQL query, but I would really like to be able to use
stored procedures.

As far as I know, I have 2 options: (1) Pass in “where clause†string to SP
(2) Execute SQL Query inside ASP.NET

Are there any other options? If anyone ever had to implement something
similar, I want to hear your comments. Thanks

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Hello,

I am developing ASP.NET 1.1 application against SQL Server 2000 db, where
users have ability to construct their own select criteria (for a where
clause). I already developed a user control where user can select field
name,
operator (equals, like, begins with, etc) and value of the search
criteria.
This control can already build where clause part. Now I am at the point,
where I need to execute SQL query, but I would really like to be able to
use
stored procedures.

You cannot use a SP for this, the query will change all the time therefore
the engine cannot precalculate the execution path of it. you will have to
submit it as CommandType = Text

One warning I give you is taht you have to be careful with SQL injection,
the query can be injected both in the select as well as in the where clauses

 

[Karl Seguin [MVP]]

Sure you can. stored procedures can dynamically execute sql via a number of
functions, include exec and sp_execute.You do lose out on some of the sproc
benefits though (such as cached execution plans)

I'm not sure which I'd use...the difference between the two at the point you
are at is pretty minimal...

Here's an *must read* guide for anyone writing dynamic sql

http://www.sommarskog.se/dynamic_sql.html

Karl

 

[sloan]

Check this article:

http://www.sqlservercentral.com/columnists/sholliday/thezerotonparameterproblem.asp

(which I wrote)

You're going to get some "dynamic sql is ok", but I don't like it. Please,
I know the arguments, and don't need another round of it. (or others
reading).

You can use your select screen to create the Xml, which gets passed into the
procedure.

...

If you're using Access. then you have no choice, but to build the SQL.

If you're using Oracle, the last time I used Oracle 9, it was very very
cumbersome to use their XML model.

...

 

[Chip]

You can definitely create dynamic SQL in an SPROC. And I don't beleive you
do suffer from the caching issue (an urban myth these days as SQL no longer
"precompiles" procs, but instead stores execution plans for all queries).
Here is a working example.

create proc RepMetaStateBookList
@year char(5),
@range varchar(10)
as
declare @sql varchar(2000)
set @sql = '
select top 100
Author,
Title,
count(*) Lists
from list l
join list_detail d on l.listID = d.listID
join book b on d.bookID = b.bookID
join author a on b.authorID = a.authorID
where ( list_name like ''%:%' + @year + '%'' )
and left(list_name, 2) in (select code from arlist_dev..state)
' +
case
when @range = 'PK-2' then ' and cast(level_atos as float) <= 2.9 '
when @range = '3-5' then ' and cast(level_atos as float) between 3.0 and
5.9 '
when @range = '6-8' then ' and cast(level_atos as float) between 6.0 and
8.9 '
when @range = '9-12' then ' and cast(level_atos as float) >= 9.0 '
when @range = 'ALL' then ''
end
+ '
group by author, title
having count(*) > 1
order by count(*) desc, author, title '

exec ( @sql )

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

"Karl Seguin [MVP]" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
net> wrote in message news:%[email protected]...

Sure you can. stored procedures can dynamically execute sql via a number
of functions, include exec and sp_execute.You do lose out on some of the
sproc benefits though (such as cached execution plans)

IMO it would be the same, just adding another layer ( has to invoke the
SP ).

I'm not sure which I'd use...the difference between the two at the point
you are at is pretty minimal...

Here's an *must read* guide for anyone writing dynamic sql

http://www.sommarskog.se/dynamic_sql.html

Very good article, thanks for the link

 

[Vijay14s]

Building SQL query dynamically

I am developing a power search page in asp.net 2.0 and sql server 200.
Can you provide the sample code that you implemented to collect user selected search criteria[field name, operator (equals, like, begins with, etc)]
Very thank full if provided ASAP

Hello,

I am developing ASP.NET 1.1 application against SQL Server 2000 db, where
users have ability to construct their own select criteria (for a where
clause). I already developed a user control where user can select field name,
operator (equals, like, begins with, etc) and value of the search criteria.
This control can already build where clause part. Now I am at the point,
where I need to execute SQL query, but I would really like to be able to use
stored procedures.

As far as I know, I have 2 options: (1) Pass in “where clause†string to SP
(2) Execute SQL Query inside ASP.NET

Are there any other options? If anyone ever had to implement something
similar, I want to hear your comments. Thanks