= NULL vs IS NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

= NULL vs IS NULL

Author

Topic

krazykarter
Starting Member

4 Posts

Posted - 2010-02-16 : 11:08:31

I understand that we should use IS NULL instead of = NULL but forgot, which had an interesting result which I was hoping someone could explain for me.

I created a stored procedure which used an nvarchar to build a select query, and used sp_executesql to execute this string.
Part of the query was:
SELECT * FROM [countries] WHERE [ID] = NULL

When I executed the stored procedure, this query returned multiple rows. However, when I ran the query directly in SQL query analyzer, no results were returned.

Why is that?

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 11:10:39

Different ANSI_NULLS setting in whatever you used to Exec the SProc, as compared to the Query Window that you used in QA.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-16 : 11:12:11

Your stored proc probably had the ANSI_NULLS OFF

http://msdn.microsoft.com/en-us/library/ms188048.aspx

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-16 : 11:12:37

krazykarter
Starting Member

4 Posts

Posted - 2010-02-16 : 11:12:39

The stored proc was executed in the same query analyzer.

Vijay, that seems to be what happened.

Thanks.

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 11:22:30

Note that SET ANSI_NULLS = OFF is deprecated (Hurray!)

Perhaps ANSI_NULLS is set within the SProc?

krazykarter
Starting Member

4 Posts

Posted - 2010-02-16 : 11:29:30

SET ANSI_NULLS OFF was added automatically by SQL Server Enterprise Manager. The tricky part is that when you double click on a SProc to open it, this line does not show. It only shows when you copy the SProc (not the physical text in the SProc).

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 12:09:19

SET ANSI_NULLS is a runtime option (not a parse option) so it has to be within the body of the SProc to work, so I don't quite see how Enterprise Manager can be adding it. Perhaps it is being run earlier, but in the same session as the SProc?

Did you perhaps mean Query Analyser (rather than Enterprise Manager)? That has a:

Tools : Options : [Connection Properties] : "Set ANSI_NULLS"

setting (which is ticked by default), which might be set differently for the window in which you ran the SProc?

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 12:14:19

Ah ... maybe you are getting this when you "copy" the Sproc from Enterprise Manager


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF
GO

... Sproc code here ...

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if so I'm not sure how that is getting set, its not the default, but maybe it is a setting on your database or server?

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 12:16:05

Yeh! Right click Database : Properties : [Options] : "ANSI NULL default" - normally UNchecked.

krazykarter
Starting Member

4 Posts

Posted - 2010-02-16 : 13:25:59

quote:
Originally posted by Kristen

Ah ... maybe you are getting this when you "copy" the Sproc from Enterprise Manager

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

... Sproc code here ...

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if so I'm not sure how that is getting set, its not the default, but maybe it is a setting on your database or server?

That is exactly what I get.
I'm not too worried about changing it, I was just wondering why = NULL worked when running the SProc, but not when running the query directly.

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 13:54:13

Yup, I can understand not wanting to change how it current is

But keep in mind that SET ANSI_NULLS OFF is deprecated ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources