Stored Procedure with Optional Parameters

Question

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'

Answer 1

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.

Answer 2

What you're looking for is dynamic SQL: the ability to dynamically build a query based on different parameters.

A few great starting points include:

• Kitchen Sink Procedures by Aaron Bertrand - how to write a query when your users might be asking for anything out of a huge table
• Dynamic Search Conditions by Erland Sommarskog - which goes into more depth
• Curses and Blessings of Dynamic SQL by Erland Sommarskog - but don't read this until you've read the above two, because dang, it goes into even more details
• Dynamic SQL by Brent Ozar - if you prefer learning with slides and following along with Stack Overflow's database, there's this (disclaimer: I wrote it)

One thing to look out for, though, is that anytime you pass end user inputs into a database query, you can be vulnerable to SQL injection. A few resources on that:

• SQL Injection in Microsoft Books Online - pretty good one-page summary of what the problem is, and how to look out for it.
• SQL Injection Attacks: Is Your Data Secure by Bert Wagner - video from GroupBy
• Bobby Tables - classic XKCD comic about the problem of SQL injection. I only mention this because the other resources are all going to refer to Bobby Tables, and you're going to wonder why everyone's laughing.