Select specific items, OR select all

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)

Select specific items, OR select all

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2009-07-22 : 00:01:16

How do I create one SQL Statement to do the following.

I have an input parameter @CategoryID and @SizeID. I need to use both of them to filter a products table.

How do I create an SQL statement (without using Dynamic SQL, or multiple conditional statements) that will allow me to filter by the above criteria based on the possibility of the incoming parameters having a value, or being null (or either combination of the two).

ie

@CategoryID = NULL
@SizeID = NULL

@CategoryID = 1
@SizeID = NULL

@CategoryID = NULL
@SizeID = 3

@CategoryID = 4
@SizeID = 6

--EDIT--

I've got a solution. I am not sure if it is the most efficient but it works

SELECT ProductName, Price FROM Products WHERE (CategoryID = @CategoryID OR @CategoryID IS NULL)
AND ([Size] = @SizeID OR @SizeID IS NULL)

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-07-22 : 00:25:10

Or U can use <code>
SELECT ProductName, Price
FROM Products
WHERE CategoryID = COALESCE(@CategoryID, CategoryID)
AND [Size] = COALESCE(@SizeID, [Size])</code>

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-22 : 00:41:13

Just a note. If there are rows in the table where CategoryID contain NULL value, the statement

CategoryID = COALESCE(@CategoryID, CategoryID)

will be false and the row will not be selected

KH
[spoiler]Time is always against us[/spoiler]

darenkov
Yak Posting Veteran

90 Posts

Posted - 2009-07-22 : 00:43:20

ok thanks.

any ideas on which method is best for performance?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-07-22 : 00:47:34

check this link
http://weblogs.sqlteam.com/jeffs/archive/2007/09/18/sql-conditional-where-clauses.aspx

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-22 : 00:56:52

that blog point you to another that is more appropriate. http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

KH
[spoiler]Time is always against us[/spoiler]

darenkov
Yak Posting Veteran

90 Posts

Posted - 2009-07-22 : 01:56:19

thanks all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources