July 15, 2008 at 12:03 pm
I have a table tbl1 with columns name and number.
DECLARE @number int
SELECT * FROM tbl1 WHERE number=@number
In this case I will get all the rows for that given particular number. But I want a query such that If pass a particular number I need to get the all the rows for that number and if I pass null then I need to get all the rows in the table.
How can do this?
Thanks.
July 15, 2008 at 12:05 pm
If the variable is null set the number to itself.
SELECT * FROM tbl1 WHERE number=ISNULL(@number,number)
If the number can be null you have to wrap an isnull around it too since null does not equal null.
SELECT * FROM tbl1 WHERE ISNULL(number,1)=ISNULL(@number,ISNULL(number,1))
Or you can use Coalesce.
SELECT * FROM tbl1 WHERE ISNULL(number,1)=Coalesce(@number,number,1)
July 15, 2008 at 12:21 pm
Thank you so much.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply