How to build conditional WHERE clause

[mabho]

Hi. Sorry if it is a silly question.

I want to build a conditional WHERE clause for a stored procedure.

My procedure has some attributes like
@param1
@param2

These params correspond to filters in my web application. If users select an item from a ListBox, the application will populate these params with the corresponding key for filtering the field. But if users DO NOT select a filter in these list boxes, leaving like -- ALL --, I don't want the stored procedure to filter them. In a very poor way (I know this is not the correct syntax), I will give an example:

PROCEDURE XXX
@param1 int,
@param2 int
AS
SELECT * FROM Table1

IF @Param2 <> 0 THEN (0 would correspond to ALL)
WHERE field1 = @Param1 AND field2 = @Param2
ELSE
WHERE field1 = @Param1

==== Filter for @PAram2 would only work if user passed a valid key for it. Otherwise, application would pass a '0' that the PROCEDURE would understand as a message not to filter.

How can I do that??

 

[mrdenny]

Doing it in this method would require using dynamic SQL which isn't recommended.

Something like this should do it nicly.

create procedure xxx
     @param1 int,
     @param2 int
AS
select *
from Table1
where ((@param2 <> 0 and field2 = @param2) or (@param2 = 0))
     and field1 = @Param1
go

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mabho]

Great! That really worked! Thanks a lot.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[hemanthika72]

You better read Structured Query Language (SQL): a Practical Introduction ebook This will help you to understand this type of issues. You can dowload this book from

http://www.erasoftonline.com

http://www.erasoftonline.com/erasoftonline/modules/BestFreeSqlBooks/