Dynamic SP practice - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic SP practice

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-05-04 : 11:55:42

Is it a "best practice" to have dynamic stored procedures?

For example, SUPPN0 = supplier number but sometimes I would like to execute the SP without the @SUPPNO and somtimes I would like to filter it by a total different column @BUYNO buyer number instead of SUPPNO.

How can I accomplish something like this...
CASE 1
(
@SUPPNO Varchar(50),
@STARTDATE SmallDateTime,
@ENDDATE SmallDateTime
)
CASE 2
(
@STARTDATE SmallDateTime,
@ENDDATE SmallDateTime
)

CASE 3
(
@BUYERNO Varchar(50),
@STARTDATE SmallDateTime,
@ENDDATE SmallDateTime
)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-04 : 13:23:15

Generally speaking I would say it is NOT best practice to have SPs that execute dynamic statements. Reasons include:

* Can't always leverage cached plans.

*You need to seperately grant and maintain select privs on individual tables and views because the statements don't operate in the context of the SP (grant exec on SP to <someaccount> is all you need for non-dynamic code.

* Depending on how you structure the calls and the extent of parameter validation, you can open yourself up to sql injection problems.

As for the how tos, see some of the topics and articles here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=dynamic

Be One with the Optimizer
TG

twhelan1
Yak Posting Veteran

71 Posts

Posted - 2006-05-04 : 13:25:42

The way I usually do this is something like :


CREATE PROCEDURE myProc
    @SUPPNO varchar(50) = null,
    @BUYERNO varchar(50) = null,
    @STARTDATE smalldatetime,
    @ENDDATE smalldatetime
AS

SELECT stuff 
FROM myTable
WHERE (supplier = @SUPPNO OR @SUPPNO is null) 
  AND (buyer = @BUYERNO OR @BUYERNO is null)
  AND date >= @STARTDATE 
  AND date <= @ENDDATE

Then to handle your cases, you simply don't pass a buyerno or a suppno when that's not required.

~Travis

Kristen
Test

22859 Posts

Posted - 2006-05-04 : 13:40:28

If you DO have to use Dynamic SQL then best to do it, parameterized, using sp_ExecuteSQL so that the query plan gets cached etc.

Kristen

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-05-04 : 16:21:26

Thanks everyone for the help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-05 : 04:00:36

Read more about Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-05-08 : 08:34:11

Is there a better approach then dynamic SQL?
If the requirements where I did the option to filter the query on supplier, or buyer, or none at all, would be the best approach? Write 3 seperate SPs? Like this...

SELECT *
FROM table

SELECT *
FROM Table
WHERE Buyer = Buyer

SELECT *
FROM Table
WHERE Suppluer = Suppler?

twhelan1
Yak Posting Veteran

71 Posts

Posted - 2006-05-08 : 10:16:04

See my post above, it was a suggested method that does not use dynamic SQL yet still accomplishes your goal of filtering the query by different fields. Writing seperate SP's is also acceptible if it makes sense to do so, i.e. it doesn't add complexity to your front end code with multiple if statements to handle all the different possible scenarios.

~Travis

peterlemonjello
Yak Posting Veteran

53 Posts

Posted - 2006-05-08 : 11:24:36

Here's twhelan1's sql modified to use to coalesce function. I found this works better than using the 'or is null' approach.

CREATE PROCEDURE myProc
@SUPPNO varchar(50) = null,
@BUYERNO varchar(50) = null,
@STARTDATE smalldatetime,
@ENDDATE smalldatetime
AS

SELECT stuff
FROM myTable
WHERE supplier = COALESCE(@SUPPNO, supplier)
AND buyer = COALESCE(@BUYERNO, buyer)
AND date >= @STARTDATE
AND date <= @ENDDATE

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-05-08 : 11:26:16

Thanks! I will try out both methods

Kristen
Test

22859 Posts

Posted - 2006-05-08 : 11:33:40

The style of

WHERE supplier = COALESCE(@SUPPNO, supplier)

won't work if @SUPPNO is NULL (i.e. "don't care") and the field [supplier] allows NULLs (unless you mess with the normal setting of ANSI_NULLs or whatever it is that lets you do A = B and gives TRUE if both are NULL - which is NOT the normal behaviour).

Hence twhelan1's approach:

WHERE (supplier = @SUPPNO OR @SUPPNO is null)

is preferred (actually I would swap those as I have read it can help the optimiser):

WHERE (@SUPPNO is null OR supplier = @SUPPNO)

Kristen

peterlemonjello
Yak Posting Veteran

53 Posts

Posted - 2006-05-08 : 14:47:18

Great point Kristen! I assumed the columns weren't nullable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources