April 16, 2012 at 1:44 am
HI,
DECLARE @ID INT
SET @ID = NULL
DECLARE @TABLE TABLE(fldID INT)
INSERT INTO @TABLE
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT NULL
From the above example, i want to return all the records if i pass NULL value to my parameter (@ID) and return the extract row if i pass the 1 or 2.
SELECT * FROM @TABLE WHERE fldID = 1 /* @ID = 1 */
fldID
----
1
SELECT * FROM @TABLE WHERE fldID = @ID /* @ID = NULL */
fldID
----
1
2
NULL
Can any one help me on this?
April 16, 2012 at 1:50 am
Try this:
SELECT *
FROM @TABLE T
WHERE ISNULL(T.fldID, '') = CASE WHEN @ID IS NULL THEN ISNULL(T.fldID, '') ELSE @ID END
April 16, 2012 at 2:46 am
April 16, 2012 at 2:50 am
ColdCoffee (4/16/2012)
Try this:
SELECT *FROM @TABLE T
WHERE ISNULL(T.fldID, '') = CASE WHEN @ID IS NULL THEN ISNULL(T.fldID, '') ELSE @ID END
Thanks Man. Thanks for your reply. I got it now. 🙂
April 16, 2012 at 3:38 am
ColdCoffee (4/16/2012)
Try this:
SELECT *FROM @TABLE T
WHERE ISNULL(T.fldID, '') = CASE WHEN @ID IS NULL THEN ISNULL(T.fldID, '') ELSE @ID END
Be very very careful with this sort of approach. In 2008 you could specify "recompile", but in 2005 you should be using dynamic SQL to ensure the best plan is used.
Note to self: read whole thread before replying.
Gianluca Sartori (4/16/2012)
It's a typical catch-all query. I recommend doing this with dynamic SQL.See this post[/url] on Gail Shaw's blog for more info and a better solution.
That is the excellent resource that I've used in the past as the base for writing queries based on this sort of idea.
April 16, 2012 at 9:45 am
If the input values are either NULL or some value, the we can use this as well.
IF @ID IS NULL
BEGIN
-- We know we need everything when @ID is null
-- thus we can just use this , anyways a table scan
-- is waht we actually can get out of this
SELECT *
FROM @TABLE T
END
ELSE
BEGIN
-- This shall for sure use Index, if one exists
-- and the chance of just an index seek is very high
SELECT *
FROM @TABLE T
WHERE T.fldID = @ID
END
Comments in comments 🙂 I hope this produces a decent plan too, and will always be consistent. Using dynamic SQL will also going to give us the same execution plan i believe (havent test it thou)
April 17, 2012 at 8:46 am
Maybe
SELECT * FROM @TABLE T
WHERE T.fldID = @ID or @ID is null
does good work too.
April 17, 2012 at 10:11 am
adrian.facio (4/17/2012)
MaybeSELECT * FROM @TABLE T
WHERE T.fldID = @ID or @ID is null
does good work too.
Sorry, but this is a HORRIBLE recommendation!! Please read the referenced link on Catch-All queries earlier in this thread. This type of query is GUARANTEED to get you BAD performance on some/many of your queries!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 17, 2012 at 10:39 am
That is why said maybe haha, i will check it out.
April 17, 2012 at 10:44 am
You are rigth, it produces table scan.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply