Stored Procedure best practice

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

Transact-SQL (2005)

Stored Procedure best practice

Author

Topic

alockrem
Starting Member

18 Posts

Posted - 2007-07-17 : 13:03:01

I have read the best practice document on SQLTeam.com but didn't find an answer to the question I have.

In a CRM application there are many similar calls to the database and I need to know the most efficient way to manage these calls. Should I create 1 stored procedure and adapt it to all similar requests or should each request have its own stored procedure?

Example -
SELECT SaleID, QuotaValue
FROM tblSales

SELECT SaleID, QuotaValue
FROM tblSales
WHERE SalesRep = 'John Doe'

SELECT SaleID, QuotaValue
FROM tblSales
WHERE SalesMgr = 'Manager 1'

SELECT SaleID, QuotaValue
FROM tblSales
WHERE Product = 'Dog Food'

Should I have stored procedures called:

proc_GetAllSales
proc_GetSalesBySalesRep
proc_GetSalesBySalesMgr
proc_GetSalesByProduct

or should I have a stored procedure called

proc_GetSales

and have if / case statements in the stored procedure to handle the different types of requests?

Thanks for your help.

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-07-17 : 13:15:56

Better to use single proc with WHERE clauses like -

WHERE Product=@Product OR @Product IS NULL
AND SalesRep = @SalesRep OR @SalesRep IS NULL

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-17 : 13:21:48

quote:
Originally posted by cvraghu

Better to use single proc with WHERE clauses like -

WHERE Product=@Product OR @Product IS NULL
AND SalesRep = @SalesRep OR @SalesRep IS NULL

rather the other way for a better query plan..


WHERE (@Product IS NULL OR Product=@Product )
AND (@SalesRep IS NULL OR SalesRep = @SalesRep )

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-17 : 13:37:59

better query plan?

since when does SQL Server have condition evaluation shortcuting?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-17 : 13:56:22

As Erland says, there is a chance of table scans as well as parameter sniffing. He has more explanation here: http://www.sommarskog.se/dyn-search.html

I was actually able to validate that and I did notice scans in my test scenarios.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-17 : 14:03:04

thanx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

alockrem
Starting Member

18 Posts

Posted - 2007-07-17 : 14:03:19

Thank you both for your help. I have never seen that approach which is why I am not currently using it. It seems very easy to use and very easy to expand on.

I appreciate all of the help.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-17 : 14:06:38

It depends on how many variations you will have. If you create a stored procedure specific for each type of query, you are likely to get better query plans. If you have so many variations that the combinations are unmanageable, you have a couple of options.

One way is to have a query, like the example posted by Dinakar, where you have nullable parameters:

where (COL1 = @PARAM1 or @PARAM1 is null) and (COL2 = @PARAM2 or @PARAM2 is null ) …

Another way is to build the WHERE clause of the query dynamically, depending on the parameters that are passed to the procedure. You may get a better query plan this way, but the user running the query will have to have direct select permission on the tables, and you won’t be able to take advantage of query plan caching.
Example:

Declare @sql varchar(8000)
set @sql =
case when @PARAM1 is not null then 'where COL1 = '''+@PARAM1+''' ' else null end
set @sql =
case when @PARAM2 is not null then isnull(@sql+' and','where'),' COL2 = '''+@PARAM2+''' ' else @SQL end
set @sql =
case when @PARAM3 is not null then isnull(@sql+' and','where'),' COL3 = '''+@PARAM3+''' ' else @SQL end

CODO ERGO SUM

alockrem
Starting Member

18 Posts

Posted - 2007-07-17 : 14:12:24

Thank you Michael. I believe my situation will be easy enough to manage so I am going to try the @PARAM IS NULL approach first.

I thought I read in the best practices document that Declare @sql is a bad idea. I glanced over that section so I don't know why it said that. I could be wrong though.

Thanks again everyone.

alockrem
Starting Member

18 Posts

Posted - 2007-07-17 : 14:19:01

This may need to be a new thread...

When I attempt to run a stored procedure from C# it requires me to have all of the parameters within the stored procedure defined in the C# code. How would a value ever be NULL in the stored procedure? Would I just define the parameter and not assign it a value in the C# code?

Thanks again.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-17 : 14:45:35

You need to allow NULLs in the proc. Set the default value as NULL in the proc.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

pootle_flump

1064 Posts

Posted - 2007-07-18 : 05:37:25

quote:
Originally posted by Michael Valentine Jones

you won’t be able to take advantage of query plan caching.

Adam Machanic has a nice article on this that allows the best of both worlds (dynamic where clause & plan caching):
http://www.sqljunkies.com/WebLog/amachanic/articles/StoredProcedureCaching.aspx
It also mitigates for SQL Injection problems.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources