Imagine we have the following parameters to do a search passed into a SPROC:
manufacturer, model, location, type
Now, I need to do a search on these parameters where values are supplied. I know we can use a dynamic SQL concat and execute it in the SPROC but maybe messy and not optimised.
For instance, they may pass in ALL parameters therefore I have to do a search on all the tables or fields.
But if they pass in just Manufacturer AND type or maybe they pass in model AND manufacturer.... (and so on).... different queries are needing to be executed.
Question is, what is the best way of creating a SPROC to handle this with different combinations and not having to use dynamic SQL?
There are several ways. One is to check the parameters as the first step(s).
You could set a default for the parameters.
You could write the criteria to handle NULL parameters.
The best way to do this is to use dynamic SQL. Since that's not possible here, the next best thing to do is to write the WHERE clause to do parameter checks and to specify OPTION ( RECOMPILE ) on the query:
WHERE
(@manufacturer IS NULL OR manufacturer = @manufacturer) AND
(@model IS NULL OR model = @model) AND
(@location IS NULL OR location = @location) AND
(@type IS NULL OR type = @type)
...additional query code...
OPTION ( RECOMPILE )
What if the parameter is null? Does that mean it will check the field for a null value also? In which case I wouldn't want to do that.
what about the case of possibly joining tables?
really seems dynamic SQL is the way here, without doing the tedious task of a check for different combination of parameters....