1

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'
0

2 Answers 2

7

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.

7

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:

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.