Beginner question Searching table

I have a stored procedure that takes 5 parameters. Based on what parameters are passed i want to return a result set to the calling code. Not all of the parameters are required so there will be some combinations on the entered parameters.

Do i need to dynamic build the query with the IF statements to check if the input parameter is NULL or not ?

Thanks

You can use the isnull function like so:

-- Using the northwind database in sql server 2k

declare @.categoryname varchar(100)
declare @.categoryID varchar(100)

--set @.categoryname = 'Produce'
set @.CategoryID = 4

select * from categories
where isnull(@.categoryname, categoryname) = categoryname
and isnull(@.categoryID, categoryID) = categoryID

|||For additional reading on this subject, check this article for a full description of was to do this: http://www.sommarskog.se/dyn-search.html|||

Hi,

Instead of running the below one

select * from categories
where isnull(@.categoryname, categoryname) = categoryname
and isnull(@.categoryID, categoryID) = categoryID

Running this one may be faster

select * from categories
where (@.categoryname is null or categoryname = @.categoryname)
and (@.categoryID is null or categoryID = @.categoryID)

Since during the execution plan creation the engine may decide a better way since it knows that "@.categoryname is null" is true for every record. But a similar approach is also valid for the first one too. Actually this is related with how wise the sql server engine is.

Eralper

http://www.kodyaz.com

|||

Thanks all for helping. It works great now and it runs fast for me. Thanks so much for you help.

Guess i need to start reading up on my T SQL.

|||

The query optimizer doesn't quite optimize the conditions using the multiple variables like you described. The problem is that the value of only @.categoryname or @.categoryid will be fixed at compile time depending on the first execution and the plan primed into the cache will not be that efficient for the other invocations. Erland's article that Louis posted goes through lot of techniques and their various pros/cons. The recompilation whitepaper below is also a good read on how plan caching works:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx