If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Stored procedure: Dynamic WHERE clause?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2008
Posts: 52
Stored procedure: Dynamic WHERE clause?

I want to write a stored procedure that takes, let's say for example, six parameters. None, one, or several of the parameters may be specified by the caller. I came across an article which discuss some different techniques for doing this. What's not clear to me is which is a recommended way to do this?

The article is:
http://www.codeproject.com/KB/databa...RE_Clause.aspx

In a nutshell the methods proposed in the article are:

Using COALESCE:

where EmployeeName = Coalesce(@EmployeeName, EmployeeName) AND
JoiningDate >= Coalesce(@StartDate, JoiningDate) AND
JoiningDate <= Coalesce(@EndDate, JoiningDate) AND
Salary >= Coalesce(@Salary, Salary)

Using ISNULL:

where EmployeeName = IsNull(@EmployeeName, EmployeeName) AND
JoiningDate >= IsNull(@StartDate, JoiningDate) AND
JoiningDate <= IsNull(@EndDate, JoiningDate) AND
Salary >= IsNull(@Salary, Salary)

Using CASE:

where EmployeeName =
Case When @EmployeeName Is Not Null Then @EmployeeName
Else EmployeeName End AND JoiningDate >=
Case When @StartDate Is Not Null Then @StartDate
Else JoiningDate End AND JoiningDate <=
Case When @EndDate Is Not Null Then @EndDate
Else JoiningDate End AND Salary >=
Case When @Salary Is Not Null Then @Salary
Else Salary End

Alternative:

WHERE (@EmployeeName Is Null OR @EmployeeName = EmployeeName) AND
(@Salary Is Null OR @Salary = Salary) AND
(@StartDate Is Null OR @EndDate Is Null OR
(@StartDate Is Not Null AND @EndDate Is Not Null AND
JoiningDate BETWEEN @StartDate AND @EndDate))

and also there is constructing the sql statement by string concatentation.

Which would you use?
Reply With Quote
  #2 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,556
I use your alternative method, however...
...if the table is large or there are a great many option parameters which are rarely used, then you will get greater efficiency constructing a dynamic sql statement containing only the parameters specified.
This is one of the few instances where dynamic SQL is preferable to coded SQL.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2003
Location: San Antonio, TX
Posts: 3,634
Hmmm...Dynamic SQL? What about security? I would try to separate the logic into independent modules, even though I already hear a bunch of arguments against it. The cleaner way is to review the design of the application itself. Having a "do-it-all" stored procedure is neat from a developer's stand point, but it's a bad idea from database performance view.
__________________
"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."
Reply With Quote
  #4 (permalink)  
Old
another indirection layer
 
Join Date: May 2004
Location: Seattle
Posts: 1,313
this is pretty exhaustive on the topic:

http://sommarskog.se/dyn-search.html
__________________
elsasoft.org
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2008
Posts: 52
Quote:
Originally Posted by rdjabarov
The cleaner way is to review the design of the application itself. Having a "do-it-all" stored procedure is neat from a developer's stand point, but it's a bad idea from database performance view.
I'd be happy to do it another way if there's a better practice. The stored procedure would drive an ASP.NET gridview which would need to be optionally filtered by several of its columns. The user could select to filter by any combination of parameters including no filtering at all. If you have another solution I'm open.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Mar 2008
Posts: 52
Quote:
Originally Posted by jezemine
this is pretty exhaustive on the topic:

http://sommarskog.se/dyn-search.html
Thanks for the link I did come across that article in my googling.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On