Want to create a stored procedure that searches for specific, but optional, values? I had to do it today for a web project I'm working on I hate not being able to talk about projects I'm working on and Terry Denham, the SQL Guru without a blog, came to my rescue yet again.

Saved my butt again.

How to create a SQL Server stored procedure with parameters

The procedure above was updated to correct a logical flaw in Terry's example. While not as performant as Dynamic SQL blah blah blah it does the trick in situations like mine where direct table operations are not allowed for security reasons.

Hey Rob, which one has the better execution plan, the one that you shared or this: Another variation to consider is anyone's doing execution plans: WHERE Param1 IN Col1, NULL Shouldn't is be something like the following? This makes no sense, did you actually test it? The option Darren offers works, but only if there are no NULL values in the columns.

Ramon's sql is what I've always seen and used.

And I don't think that is what you are after. The only problem that I have with optional parameters for search storedprocedures is that they will degrade your query performance dramatically if you use a lot of paramters.

It is just better build the query in some sort of dataaccess component. Because SQL server will save the execution plan performance won't suffer from this.

Pass Tables to Stored Procedure / Table Valued Parameter: ASP Alliance

Only problem with this is that some architects think this is wrong.. Like Ramon, this is how I would do it: AFAIK Ramon's code does handle null columns properly.

Too bad, as COALESCE is a bit faster than the IS NULL OR..

sql - Check if a parameter is null or empty in a stored procedure - Stack Overflow

Simon, Unfortunately the right solution is the wrong solution because it opens up an unacceptable attack vector on my application. I do not allow any direct operations on my tables. The link you provided stated the security risk up front, and said it may not be acceptable for that very reason.

Given the requirements, your option is not valid. Toggle navigation Robert McLaws: Home About Sign In. Optional Parameters in SQL Stored Procedures Wednesday, February 18, Paul Bartlett - Wednesday, February 18, Ramon Smits - Wednesday, February 18, Paul Wilson - Wednesday, February 18, JosephCooney - Wednesday, February 18, 4: Robert McLaws - Wednesday, February 18, 5: Terms Of Use - Powered by Orchard.