Stored Procedure with Optional Parameters I have a View that grabs a lot of data that I want to be able to Search through.  The View is set up with the following columns:


  Part_Number, Description, Information, Supplier, Manufacturer, Category, Subcategory, Currency, Price, Discount.


I am looking to create a Stored Procedure which lets me search through the View for the data I am specifically looking for.  Something like:

SELECT * FROM PartsData
WHERE Part_Number = '0010.05.20.20'


Now my first thought, was to set up the procedure so that the only parameter it was looking for was a VARCHAR which would essentially contain whatever the entire WHERE clause was.  This sounds like it would work.

However, would I be able to instead set up a Stored Procedure with optional parameters?  I am not sure if this is something you can do in SQL though.

If not, is my initial idea for implementation my best bet?  Because it seems like there should be a better way to go about doing this. :\

Sometimes the WHERE clauses can be rather long, such as:

WHERE Description LIKE '%cap' 
AND Supplier LIKE 'A2A Systems' 
AND Manufacturer LIKE 'Vario'
I wouldn't create a proc on a view, but instead just use the proc against the underlying query itself.

In your example you use LIKE instead of = which adds a level of complexity to the mixture. Usually this could be handled like:

WHERE 
(Description = @desc or @desc is null)
AND (Supplier = @supplier or @supplier is null)
AND (Manufacturer = @manufacturer or @manufacturer is null)


With LIKE you'd have to concatenate the % on the head and tail end (or just one end if that suffices). Brent has a post about performance about LIKE

WHERE 
(Description like '%' +  @desc + '%' or @desc is null)
AND (Supplier = '%' + @supplier + '%' or @supplier is null)
AND (Manufacturer = '%' + @manufacturer + '%' or @manufacturer is null)


Lastly, this is commonly referred to a kitchen sink query, or catch all query. Here's Aaron's blog about performance with this type of query.