June 12, 2009 at 2:09 am
Hiiiiiiiii
I have a problem like below:-
i have a table like below
Id Name
1 David
2 Paul
Null Dave
i am getting values of this table through a sp which takes @Id as a input.
Now my problem is that i want to retrieve rows not containing NULL in ID field when input parameter @Id is not null, otherwise all entries will be allowed
it should be something like below
where id =(Case when @Id is not null then else.... end)
plzz Help me....
June 12, 2009 at 3:03 am
Try with this:
SELECT *
FROM table
WHERE Id = COALESCE(@id,Id,-1)
I chose -1 as a non existent value for Id to exclude NULL values for Id column, but this depends on the data you are storing.
Please note that working this way you won't always get the best execution plan for the query, due to parameter sniffing and other issues affecting this kind of strategy. If performance really is an issue (how many rows are there? how fast is it growing?) you may consider going with dynamic sql:
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT * FROM table '
IF @id IS NOT NULL
BEGIN
SET @sql = @sql + ' WHERE Id = @Id'
END
ELSE
BEGIN
SET @sql = @sql + ' WHERE Id IS NOT NULL'
END
EXEC sp_executesql @sql, N'@id int', @id
Hope this helps
Gianluca
-- Gianluca Sartori
June 12, 2009 at 3:06 am
You might also be interested in taking a look at this article on dynamic search conditions.
I think it is one of the best resources on the topic.
Regards
Gianluca
-- Gianluca Sartori
June 12, 2009 at 4:10 pm
try
WHERE (@id IS NULL OR id = @id)
June 13, 2009 at 12:56 am
Thanks Carrisa
Really a good logic(Short and sweet).
June 13, 2009 at 4:08 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply