Building and executing SQL Query dynamically, best practices

[WebMatrix]

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 \)]

Re: Building and executing SQL Query dynamically, best practices

Hi,

"WebMatrix" <WebMatrix@disc ussions.microso ft.com> wrote in message
news:0201AF9E-F55C-4A79-954C-F7BE3EA17120@mi crosoft.com...[color=blue]
> 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.[/color]

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



--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Karl Seguin [MVP]]

Re: Building and executing SQL Query dynamically, best practices

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
--

http://www.openmymind.net/

http://www.fuelindustries.com/

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.mach in AT dot.state.fl.us > wrote
in message news:uI$jOfXMGH A.3432@tk2msftn gp13.phx.gbl...[color=blue]
> Hi,
>
> "WebMatrix" <WebMatrix@disc ussions.microso ft.com> wrote in message
> news:0201AF9E-F55C-4A79-954C-F7BE3EA17120@mi crosoft.com...[color=green]
>> 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.[/color]
>
> 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
>
>
>
> --
> Ignacio Machin,
> ignacio.machin AT dot.state.fl.us
> Florida Department Of Transportation
>[/color]

[sloan]

Re: Building and executing SQL Query dynamically, best practices

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.

...



"WebMatrix" <WebMatrix@disc ussions.microso ft.com> wrote in message
news:0201AF9E-F55C-4A79-954C-F7BE3EA17120@mi crosoft.com...[color=blue]
> 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[/color]
name,[color=blue]
> operator (equals, like, begins with, etc) and value of the search[/color]
criteria.[color=blue]
> 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[/color]
use[color=blue]
> stored procedures.
>
> As far as I know, I have 2 options: (1) Pass in "where clause" string to[/color]
SP[color=blue]
> (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
>[/color]

[Chip]

Re: Building and executing SQL Query dynamically, best practices

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
"precompile s" procs, but instead stores execution plans for all queries).
Here is a working example.

create proc RepMetaStateBoo kList
@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..sta te)
' +
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 )



"WebMatrix" <WebMatrix@disc ussions.microso ft.com> wrote in message
news:0201AF9E-F55C-4A79-954C-F7BE3EA17120@mi crosoft.com...[color=blue]
> 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
>[/color]

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

Re: Building and executing SQL Query dynamically, best practices

Hi,

"Karl Seguin [MVP]" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
net> wrote in message news:%23bM8XkXM GHA.2580@TK2MSF TNGP14.phx.gbl. ..[color=blue]
> 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)
>[/color]

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

[color=blue]
> 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[/color]

Very good article, thanks for the link



--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation