March 20, 2012 at 8:02 am
Dear All,
More of a syntactical question here...
Background is that I have a table of staff data, including an internal id (staffid). I want to use the staffid to just pull back rows for that person. I'm quite happy with this part, and have implemented it with a declared INT variable @staffid as...
SET @staffid = 1000
SELECT * FROM TABLE WHERE Staffid = @staffid
That is the background over !
What I'd like to happen is to return all rows if the staffid is not specified, or perhaps specified in such a way as to not limit the query. I thought of a non-elegant way to achieve this, using "between" and 2 staffid variables - setting them to be the same if I just wanted 1 record or 0 to a gazillion if I wanted them all.
I wonder if anyone has a slicker solution ?
Regards, Greg.
March 20, 2012 at 8:05 am
SET @staffid = 1000
SELECT * FROM TABLE WHERE Staffid = coalesce(@staffid,staffid)
This should work. Gail Shaw has a more efficient way of handling it via secure dynamic SQL. I'll look for the link.
March 20, 2012 at 8:22 am
Genius ! Thanks very much !
March 20, 2012 at 8:49 am
To make this SARGable you should change this to not use the coalesce.
Like this.
SET @staffid = 1000
SELECT * FROM TABLE WHERE Staffid = @staffid or @staffid is null
This will let you use an index seek on Staffid instead of an index scan.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 20, 2012 at 9:25 am
With only one parameter, the dynamic SQL option is probably overkill. Here is another option that I have seen, which is fine with only one parameter.
IF @StaffID IS NULL -- or @StaffID = 0 if that works better for you
SELECT *
FROM YourTable
ELSE
SELECT *
FROM YourTable
WHERE StaffID = @StaffID
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 20, 2012 at 9:38 am
drew.allen (3/20/2012)
With only one parameter, the dynamic SQL option is probably overkill. Here is another option that I have seen, which is fine with only one parameter.
IF @StaffID IS NULL -- or @StaffID = 0 if that works better for you
SELECT *
FROM YourTable
ELSE
SELECT *
FROM YourTable
WHERE StaffID = @StaffID
Drew
Drew I am curious if you have seen performance improvements with this multiple queries over the way I posted above. Or is it just personal preference?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 20, 2012 at 10:11 am
I personally wouldn't recommend either. The @variable IS NULL OR Column = @Variable produces poor execution plans (unless option recompile is specified and the SQL version is > 2008 SP2) and is almost certain not to index seek, The IF statement produces erratic execution plans
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
With one variable only the catch-all is not too bad. With more it can be terrible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2012 at 10:17 am
Thanks for the reminder Gail. I did not realize the performance was much of an issue when there is only 1 variable with the approach I took. I knew that if there even two variables I would not go that way.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 20, 2012 at 10:56 am
Sean Lange (3/20/2012)
Drew I am curious if you have seen performance improvements with this multiple queries over the way I posted above. Or is it just personal preference?
Just to be clear, I only said that I had seen this. I haven't done any performance testing on this scenario, because all of my procedures with optional parameters have multiple parameters, so I use the dynamic SQL route.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 20, 2012 at 11:03 am
drew.allen (3/20/2012)
Sean Lange (3/20/2012)
Drew I am curious if you have seen performance improvements with this multiple queries over the way I posted above. Or is it just personal preference?Just to be clear, I only said that I had seen this. I haven't done any performance testing on this scenario, because all of my procedures with optional parameters have multiple parameters, so I use the dynamic SQL route.
Drew
Same here. I figure with a single parameter that either of these would perform reasonably well. I was just asking if you had used that particular method, which I guess you have not. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 20, 2012 at 11:34 am
Sean Lange (3/20/2012)
Same here. I figure with a single parameter that either of these would perform reasonably well.
Depends. Even with one parameter the IF is prone to erratic plans. Depending what parameter value is the one that the plan is compiled with and how many rows the query with parameter is expected to return, you can get horrid performance even with only one parameter (see the second blog post I referenced)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2012 at 2:12 pm
Gail,
I had found:
WHERE (key1 = @key1 AND @key1 IS NOT NULL)
OR (key2 = @key2 AND @key2 IS NOT NULL)
OR (key3 = @key3 AND @key3 IS NOT NULL)
makes better execution plans than:
WHERE (key1 = @key1 OR @key1 IS NULL)
AND (key2 = @key2 OR @key2 IS NULL)
AND (key3 = @key3 OR @key3 IS NULL
I guess the boolean logic forces the compiler to look inside each OR clause and consider the column inside it.
in opposition to the AND clauses since the first invalid AND clause can make the compiler to
shortcut and no evaluate the rest of the expression (and ignore the other columns).
But its just a guess and based in a long ago test in a SQL Server 7.
What do you think about it?
March 20, 2012 at 2:17 pm
jcb (3/20/2012)
Gail,I had found:
WHERE (key1 = @key1 AND @key1 IS NOT NULL)
OR (key2 = @key2 AND @key2 IS NOT NULL)
OR (key3 = @key3 AND @key3 IS NOT NULL)
makes better execution plans than:
WHERE (key1 = @key1 OR @key1 IS NULL)
AND (key2 = @key2 OR @key2 IS NULL)
AND (key3 = @key3 OR @key3 IS NULL
I guess the boolean logic forces the compiler to look inside each OR clause and consider the column inside it.
in opposition to the AND clauses since the first invalid AND clause can make the compiler to
shortcut and no evaluate the rest of the expression (and ignore the other columns).
But its just a guess and based in a long ago test in a SQL Server 7.
What do you think about it?
Those are not logically the same. In the top the AND portion is completely irrelevant. The equality operator already ensured they are not null. The second code box is handling optional parameters.
--EDIT - fat fingers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 20, 2012 at 2:55 pm
jcb (3/20/2012)
Gail,I had found:
WHERE (key1 = @key1 AND @key1 IS NOT NULL)
OR (key2 = @key2 AND @key2 IS NOT NULL)
OR (key3 = @key3 AND @key3 IS NOT NULL)
makes better execution plans than:
WHERE (key1 = @key1 OR @key1 IS NULL)
AND (key2 = @key2 OR @key2 IS NULL)
AND (key3 = @key3 OR @key3 IS NULL
I guess the boolean logic forces the compiler to look inside each OR clause and consider the column inside it.
in opposition to the AND clauses since the first invalid AND clause can make the compiler to
shortcut and no evaluate the rest of the expression (and ignore the other columns).
But its just a guess and based in a long ago test in a SQL Server 7.
What do you think about it?
CREATE PROCEDURE SearchHistoryORs
(@Product int = NULL, @OrderID int = NULL, @TransactionType char(1) = NULL, @Qty int = NULL)
AS
SELECT ProductID, ReferenceOrderID, TransactionType, Quantity,
TransactionDate, ActualCost from Production.TransactionHistory
WHERE (ProductID = @product Or @product IS NULL)
AND (ReferenceOrderID = @OrderID OR @OrderID Is NULL)
AND (TransactionType = @TransactionType OR @TransactionType Is NULL)
AND (Quantity = @Qty Or @Qty is null)
GO
CREATE PROCEDURE SearchHistoryAnds
(@Product int = NULL, @OrderID int = NULL, @TransactionType char(1) = NULL, @Qty int = NULL)
AS
SELECT ProductID, ReferenceOrderID, TransactionType, Quantity,
TransactionDate, ActualCost from Production.TransactionHistory
WHERE (ProductID = @product AND @product IS NOT NULL)
OR (ReferenceOrderID = @OrderID AND @OrderID Is NOT NULL)
OR (TransactionType = @TransactionType AND @TransactionType IS NOT NULL)
OR (Quantity = @Qty AND @Qty is NOT null)
GO
GO
EXEC SearchHistoryORs @product = 978, @TransactionType = 'W'
208 rows returned
EXEC SearchHistoryANDs @product = 978, @TransactionType = 'W'
124532 rows returned
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2012 at 7:00 am
Sorry, forgot the wrapping NOT.
Se the below snipet.
-- Boolean equivalence rules:
-- not(A and B) <=> (not A) or (not B)
-- A and B <=> not((not A) or (not B))
-- not(A or B) <=> (notA) and (not B)
declare @id int, @scale int
set @id = 3
set @scale = 0 --nullable column
select * from syscolumns
where (id = @id or @id is null)
and (scale = @scale or @scale is null)
select * from syscolumns
where
NOT(
(id != @id and @id is not null)
or (scale != @scale and @scale is not null)
)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply