Skip or Ignore where clause in SP

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

General SQL Server Forums

New to SQL Server Programming

Skip or Ignore where clause in SP

Author

Topic

ugh3012
Yak Posting Veteran

62 Posts

Posted - 2013-09-20 : 10:11:36

I am looking for best practice when passing a parameter to stored procedure that is not needed. For example, sometime the users will want the list to list only by certain state. Other times the user want all states. How can I make the SP to ignore the where clause if users want all states.

Any ideas, suggestions, best practices are appreciated.


CREATE PROCEDURE usp_Example 
    @State nvarchar(2) 
AS 

    SELECT FirstName, 
    LastName, 
    State
    FROM SomeTable
    WHERE State = @FirstName;
GO

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-20 : 10:17:23

quote:
Originally posted by ugh3012

I am looking for best practice when passing a parameter to stored procedure that is not needed. For example, sometime the users will want the list to list only by certain state. Other times the user want all states. How can I make the SP to ignore the where clause if users want all states.

Any ideas, suggestions, best practices are appreciated.

CREATE PROCEDURE usp_Example
@State nvarchar(2)
AS

SELECT FirstName,
LastName,
State
FROM SomeTable
WHERE State = @FirstName;
GO

Did you mean "State = @State" rather than "State = @Firstname"?

One way to accomplish what you want is shown below.

CREATE PROCEDURE usp_Example 
    @State nvarchar(2) = NULL
AS 

    SELECT FirstName, 
    LastName, 
    State
    FROM SomeTable
    WHERE ( State = @State OR @State IS NULL)
GO

Sometimes this approch can have perfromance implications because of bad query plans. Take a look at these articles that describe this issue and proposed solutions.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

http://www.sommarskog.se/dyn-search-2008.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources