null checking

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » null checking

null checking

Rate Topic

Display Mode

Topic Options

Author

Message

Krishna1

Posted Monday, October 08, 2012 1:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 127, Visits: 280

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

ChrisM@Work

Posted Monday, October 08, 2012 2:37 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:33 AM
Points: 5,610, Visits: 10,976

shilpaprele (10/8/2012)

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

Bhumika Jawanjal

Posted Monday, October 08, 2012 2:42 AM

SSC Rookie

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

Check this:

select *
from abc
where ISNUll(col1,'') = ISNULL(@P,'')

Post #1369671

kapil_kk

Posted Monday, October 08, 2012 4:48 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151

select * from abc
where column_name IS NULL

Post #1369710

Krishna1

Posted Monday, October 08, 2012 5:16 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 127, Visits: 280

Dear Valued member

this is not taking care of the null value in parameter

Post #1369720

kapil_kk

Posted Monday, October 08, 2012 5:52 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151

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

Post #1369737

Eugene Elutin

Posted Monday, October 08, 2012 7:24 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 2,538, Visits: 4,360

shilpaprele (10/8/2012)

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!" BigGrin

(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 »

Permissions