October 8, 2012 at 1:45 am
I have one procedure with parameter @P.
If @p= null & table abc has many rows with col1 = null, then following select does not reurn the values
select *
from abc
where col1 = @P
what should be correct the syntax( do not wan tto use set ansi null on/off).
October 8, 2012 at 2:37 am
shilpaprele (10/8/2012)
I have one procedure with parameter @P.If @p= null & table abc has many rows with col1 = null, then following select does not reurn the values
select *
from abc
where col1 = @P
what should be correct the syntax( do not wan tto use set ansi null on/off).
select *
from abc
where col1 = @P
OR (@P IS NULL AND col1 IS NULL)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 8, 2012 at 2:42 am
Check this:
select *
from abc
where ISNUll(col1,'') = ISNULL(@P,'')
October 8, 2012 at 4:48 am
select * from abc
where column_name IS NULL
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 8, 2012 at 5:16 am
Dear Valued member
this is not taking care of the null value in parameter
October 8, 2012 at 5:52 am
Hi,
sorry I forget about passing parameters..
chk this one, hope this one helps -
IF (@p = 'NULL' OR @p IS NULL)
BEGIN
SELECT * FROM abc WHERE name1 IS NULL
END
ELSE
BEGIN
SELECT * FROM abc WHERE name1 LIKE @p
END
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 8, 2012 at 7:24 am
shilpaprele (10/8/2012)
I have one procedure with parameter @P.If @p= null & table abc has many rows with col1 = null, then following select does not reurn the values
select *
from abc
where col1 = @P
what should be correct the syntax( do not wan tto use set ansi null on/off).
The correct way, to return records, would be:
select *
from abc
where col1 = @P OR (@P IS NULL AND col1 IS NULL)
Using, ISNULL function on a column would stop this query to SARGable if you have an index on Col1
Saying all the above, you should really read Gail Shaw's paper on catch-all-queries :
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply