Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

null checking Expand / Collapse
Author
Message
Posted Monday, October 8, 2012 1:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
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).
Post #1369651
Posted Monday, October 8, 2012 2:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:56 AM
Points: 7,172, Visits: 13,613
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)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1369669
Posted Monday, October 8, 2012 2:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 11:49 PM
Points: 30, Visits: 33
Check this:

select *
from abc
where ISNUll(col1,'') = ISNULL(@P,'')
Post #1369671
Posted Monday, October 8, 2012 4:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
select * from abc
where column_name IS NULL



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1369710
Posted Monday, October 8, 2012 5:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Dear Valued member

this is not taking care of the null value in parameter
Post #1369720
Posted Monday, October 8, 2012 5:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1369737
Posted Monday, October 8, 2012 7:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 2,836, Visits: 5,066
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/


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1369803
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse