SQL Server Stored Procedure with Optional Parameters

sql serverstored-procedures

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'

Best Answer

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.

Related Question