Help - Conditional Checks within a SQL Query - other ways of doing it?

I'm trying to simplify a SQL Stored Procedure.

The query accepts an int, @.ItemTypeID int

I have the Query:

SELECT ... FROM ItemList WHERE ItemTypeID = @.ItemTypeID

or, if @.ItemTypeID is 0,

SELECT ... FROM ItemList

Is there a way to do this query without doing:

IF @.ItemTypeID = 0
BEGIN
...SELECT QUERY...
END
ELSE
BEGIN
...SELECT QUERY...
END

?

Try if you can combine the query by using joins. If not, then stay with the current. A Sql stored procedure is not meant to be very readable like C#, it goes by the execution plans. Hence even the dynamica sql should be used reluctantly.

|||

select... from itemList where (@.itemTypeID = 0) or (@.ItemTypeID = ItemTypeID)

|||

Yes, although what you have will execute the fastest.

If you feel it's not worth the performance difference, you can always do this:

SELECT * FROM ItemList WHERE (ItemTypeID=@.ItemTypeID OR @.ItemTypeID=0)

|||

Ok, thanks guys.

I guess the extra

WHERE (ItemTypeID=@.ItemTypeID OR @.ItemTypeID=0)

Adds a check on each row that is returned - which slows it down. I'll keep the current ugly method :)

Just sometimes I might have to check 5-6 things... which looks very ugly.

|||

I'd be amazed if you could measure the difference.