HOW TO? Stored proc, many optional parameters
-
I want to make a stored proc that returns a list of orders from an order table where the users choose some or all filter values (or none).
So I need to build a WHERE clause in the stored proc. I'd like the proc to be compiled so dynamic SQL with an EXEC is not a good idea, I understand.
Do I really need to to build a sql statement with every combination of parameters involved or not involved? Can I use a Like '%' statement for those cases where a parameter for a string is not required? What would I do for a date or a integer parameter that the user did not specify?
Can I somehow build one SQL stement with a WHERE clause that mentions all the parameters but for the ones the user did not specify will still pass all records?
Environment SQL Server 2005.
--Trying to show two filters but I have 10
@ordernumber null,
@ClientName varchar(5)
--Both Filters in play
If @ordernumber IS NULL and @ClientName IS Null
BEGIN
SELECT * FROM orders
END
ELSE
IF @ordernumber IS NOT NULL
BEGIN
SELECT * FROM Orders WHERE Orders.OrderNumber = @ORdernumber
END
ELSE
BEGIN
SELECT * FROM Orders WHERE Orders.ClientName Like @ClientName + '%'
(there must be a better way!!)
TIA Phil
-
Well, you can always write "@parameter IS NULL OR @parameter = table.column". If parameter is null, it means no filtering on that condition:
SELECT * FROM orders
WHERE
(@Ordernumber IS NULL OR Orders.OrderNumber = @Ordernumber) AND
(@ClientName IS NULL OR Orders.ClientName Like @ClientName + '%')
It will make the WHERE clause quite complicated with 10 parameters, but it should work the way you need.
HTH, Vladan
-
-- With exact parameters:
SELECT *
FROM orders
WHERE ISNULL(@ordernumber,ordernumber)=ordernumber
AND ISNULL(@ClientName,ClientName)=ClientName
-- In case of using "LIKE"
SELECT *
FROM orders
WHERE ISNULL(@ordernumber,ordernumber)=ordernumber
AND ClientName LIKE ISNULL(@ClientName,ClientName)+ '%'
-- Test Your solution for SPEED !
R.Dragossy
-
The ufortunate by-product of this (and I have a few procs like this too) is that SQL cannot effectively use indexes. I've coded mine using Vladan's suggestion which is probably more likely to use an index than Richard's, but neither is really optimal. Dynamic SQL would be perfect from the point of view of efficient query plans, but has its own pitfalls!!
Perhaps the other way you could do it is if you have a couple of common sets of parameters that are non-null, you use if statements to execute these more targeted queries with more refined where clauses so that SQL could at least use a more targeted query and an index or two.
-
Thanks, all. Trying out the suggestions and checking for index use. Funny how you can do something a hundred times then forget because you don't do it for months. Someone said you actually remember everything, problem is the index gets deleted in your brain.
-
Phil,
Look at this article by Robert Marda: http://www.sqlservercentral.com/columnists/rmarda/howdynamicsqlcanbestatic2.asp
It helped me rewrite a dynamic query in a stored procedure into a static query with the same performance and nearly the same execution plan.
Greg
Greg
-
What a good article
I hadn't seen that one before - I guess it is probably the best (although it looks a bit tedious in a proportional font!) as SQL could evaluate the CASE statements the same way each time and *hopefully* make use of indices when they are available - haven't tried it though.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply