ASP, ADO and parameterized queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

ASP, ADO and parameterized queries

Author

Topic

dschabla
Starting Member

3 Posts

Posted - 2005-03-16 : 17:11:59

Is it possible to do parameterized queries with ASP, ADO and SQL Server 2000 without using stored procedures? If so, you anyone be kind enough to show an example?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-16 : 20:34:15

What's wrong with stored procedures?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-17 : 06:04:18

well you must use sp_executeSql to use parametrized sql Look that up in BOL = books Online = Sql server help
that will show you how it's done. if after that you don't understand something ask here.

Rob: you've never come accross a situation where parametrized sql was the way to go?
i know i have. especially with our searching where there's no way on earth i could do something usefull with a sproc.

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-17 : 07:34:10

sputter...sputter...gasp..wheeze...

quote:
there's no way on earth i could do something usefull with a sproc

clutching my heart...'Lizabeth...I'm comin' to join you....

Be One with the Optimizer
TG

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-17 : 07:34:29

Nope, I've always gone with sprocs. Takes just as much time to write, and I never have to touch app code again.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-17 : 08:12:42

ok if you can suggest me something cool using a sproc for this i'd be glad to take my words back.
TG don't go yet my friend. we're not done yet

there are posts to be answered!!!

we have a search page (now that's a big surprise)
the search always returns same 4 columns.
there are 30 input fields for a search:


[operator of choice] - can be selected from Like, not like, >, <, <=,>=, =, <>
Name	[operator of choice]	[textbox]
City	[operator of choice]	[textbox]
State	[operator of choice]	[textbox]
QTY	[operator of choice]	[textbox]
ZIP	[operator of choice]	[textbox]
...

the problem is that if all of the search fields are used about 15 different tables that are left/inner joined to eachother.
and for correct results to appear when we choose name like 'bla%' other joins shouldn't be used because it's really an overkill.

so if anyone has any bright ideas how to handle that in one sproc with one select ....

i did try to write the sproc but when i got to operator choice for each field the sproc did far worse than sp_executeSQL with just the necesary tables and conditions built dynamicaly.
i tried to do it in one select with out if else logic.

Go with the flow & have fun! Else fight the flow

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-17 : 08:15:15

Here's a question: how do you do a LIKE/NOT LIKE match on Qty?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-17 : 08:16:54

operators are used based on the type of field. i just enumerated all of them. they're not all used for every line.

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-17 : 09:18:20

I think your scenario is one of the few good examples of when to use dynamic sql (in an SP). If a specific instance of a search only uses 1 search parameter then the (dynamic) statement is small and fast. But having that work done in the SP still gives you the advantage of tweeking/optimizing the code without touching the app.

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-17 : 09:28:12

yes that's true. i agree with you on that one.
but as this app nas never even heard of sprocs (you could see tears of "joy" in my eyes when i first saw it)
i didn't bother introducing them now

i just maintain it...
but that search thing was kind of an interesting challenge for me.
so you'd put 60 parameters into a spros and build the statemnet in there and then execute it with sp_executeSQL??
i really don't see the benefit of that... not in db access or client processing.
can you show me the benefit?

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-17 : 10:20:35

These are the benifits as I see them:

maintainence/deployment
optimizations can be made to the SP without re-compiling and re-deploying application code.

encapsulation
Flexibility of changing between straight dyn sql, sp_executesql, non-dynamic sql
without the app knowing anything about it.

resouce utilization
In the places I've worked, the app developers and the db developers are 2 different animals.
The database development wasn't trusted to the people who code the applications and vice versa.
So it's not a necessity to have the developer be an expert in both sql and your app technology.

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-17 : 10:25:22

i agree that those are the benefits in general, but for this app which has only 2 sprocs and 150 asp pages that would be a waste of time

but we're on the same track on pros and cons.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-03-17 : 14:38:57

TG: Unless I have misunderstood what you are saying I would still use sp_ExecuteSQL, in the way Spirit suggests, called from with an SProc.

I think there is the world of performance difference between:


SELECT *
FROM MyTable
WHERE (@Name IS NULL OR ColName LIKE @Name)
      AND (@City ISNULL OR ColCity LIKE @City)
...

and

SET @strSQL = 'SELECT * FROM MyTable WHERE 1=1'
+ CASE WHEN @Name IS NULL THEN '' ELSE ' AND ColName LIKE @Name' END
+ CASE WHEN @City IS NULL THEN '' ELSE ' AND ColCity LIKE @City' END

EXEC sp_ExecuteSQL @strSQL, '@Name varchar(40), @City varchar(10)', @Name=@Name @City=@City

this is only really worthwhile where a) there are lots of variable parameters (and possibly sort columns too) and b) the degrading of permissions can be coped with

The reality is that commonly used combinations of queries are going to be cached, and they will be more optimal than a general-purpose-search-SProc.

The "advanced" version of the general-purpose-search-SProc is going to have temproary tables of PKs based on individual criteria, and all sorts of clever logic to try to optimise the selection process - all of which is much more efficiently handled by the SQL's Query Optimiser.

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-17 : 14:48:24

actually, in regards to ADO, you just create a command object of type "text" and in the SQL statement use "@paramname" to serve as placeholders for parameters. Then you append to the parameters collecton of that command object to set those parameters.

if you must build sql dynamically at the application layer, without a doubt this is much, much better than concatenating your values into the sql statement itself.

- Jeff

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-17 : 15:04:09

Kristen, I think you did misunderstand what I was saying. I think we're in agreement. My main point was that I think there should be SPs rather that dyn sql built and called directly from clients. That way the sql developer has control to use whatever means works best without changing the application.

quote:
I think your scenario is one of the few good examples of when to use dynamic sql (in an SP).

Spirit1's scenario was using any of 30 different search parameters plus order BYs that could potentially involve many tables. Given THAT situation when most often 1 or 2 variables are used per search and only 1 or 2 of the potentially 20 tables are needed, that dynamic sql will out perform even the most optimized statements.

Or did I misunderstand what YOU were saying? :)

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2005-03-17 : 15:16:35

I reckon we are on a different tack, but I could easily be wrong!:

I don't use dynamic SQL from the application, only Sprocs (on that I think we agree!).

However, I don't do "straight" dynamic SQL from SProcs either (which I think you are suggestion you would do in complex situations)

So instead of:


CREATE PROCEDURE MySproc
 Param1 varchar(10),
...
 Param100 varchar(10)
AS
DECLARE @strSQL varchar(8000)

SELECT @strSQL = 'Some Complex Select Syntax, based on the parameters'
EXEC (@strSQL)

I would do:


CREATE PROCEDURE MySproc
 @Name varchar(10),
 @City varchar(10),
...
 Param100 varchar(10)
AS
DECLARE @strSQL varchar(8000)

SET @strSQL = 'SELECT * FROM MyTable WHERE 1=1'
+ CASE WHEN @Name IS NULL THEN '' ELSE ' AND ColName LIKE @Name' END
+ CASE WHEN @City IS NULL THEN '' ELSE ' AND ColCity LIKE @City' END

EXEC sp_ExecuteSQL @strSQL, '@Name varchar(40), @City varchar(10)', @Name=@Name @City=@City

so that the dynamic query has a fair chance of being cached.

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-17 : 16:20:24

You're NEVER wrong, Kristen. Actually, in my point I wasn't trying to distinguish between sp_executeSQL and straight dynamic sql. I consider them both dynamic sql because you're "building" a sql statment and then EXECing it. And for the last several years I've been using sp_ExecuteSQL rather than the straight variety. (if I couldn't avoid either one)

I didn't know people from your neck of the woods used the word "reckon".

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-17 : 16:32:34

quote:
so that the dynamic query has a fair chance of being cached.

This got me thinkin'. If you executed this:

exec('Select * from myTable where Col1 = 2')
exec('Select * from myTable where Col1 = 3')

compared to this:

sp_executesql N'Select * from myTable where Col1 = @Col1', N'@Col1 int', @Col1 = 2
sp_executesql N'Select * from myTable where Col1 = @Col1', N'@Col1 int', @Col1 = 3

compared to this:

SElect * from myTable where Col1 = 2
SElect * from myTable where Col1 = 3

wouldn't the 2nd statement in all cases have the same chance of using a cached plan?

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-18 : 06:24:28

if you executed this in QA as is both statements would be cached.

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-18 : 06:28:15

you know something i personally don't think that i'd use your approach in my case Kristen.
we do build the sql string on the client and simply execute it with sp_executeSQL.
but putting all of the params into the sproc and then building it there.... i simply don't see the benefit.
if we change the search form in any way (add/remove fields) we'll have to change both app and sproc.
and there's no performance benefit by calling sp_executeSQL in a sproc vs calling it on it's own.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-03-19 : 02:59:37

Absolutely agree Spirit, in your example I too would do it direct from the application.

I would do it from an SProc if the SProc was in a position to "discover" what the query was, but certainly no point if I was just going to pass all the parameters to the SProc to them make a call to sp_ExecuteSQL !!

I would also do it in an SProc if there was a lot of additional logic - so that that was cached.

But mostly I would want to avoid any dynamic SQL so that I don't have to grant permissions at the table level.

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources