Dealing with Null in 2005 - 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

General SQL Server Forums

New to SQL Server Programming

Dealing with Null in 2005

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-02-05 : 12:13:56

Hi all,

If I want the below stm (= Null)to work in sqlserver 2005 is there some thing I need to set.

IF (@req_ty = NULL) OR (@req_ty = ' ')

-- If (isnull(ltrim(rtrim(@req_ty)),'') =''

Begin
..
..
End

Regards,
aak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 12:17:04

it should be
IF (@req_ty IS NULL)

unless your ANSI_NULLS setting is OFF

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-02-05 : 12:32:00

Thanks Visakh..

the below works I checked for both on and off.. I would like to know if this setting can be made permanant for entire database schema?

SET ANSI_NULLS OFF

Declare @var INT
SET @var = NULL

IF ( @var = NULL)
BEGIN
PRINT 'it NULL'
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 12:34:43

quote:
Originally posted by aakcse

Thanks Visakh..

the below works I checked for both on and off.. I would like to know if this setting can be made permanant for entire database schema?

SET ANSI_NULLS OFF

Declare @var INT
SET @var = NULL

IF ( @var = NULL)
BEGIN
PRINT 'it NULL'
END

not recommended to change server settings as other queries will also get affected. better to use is null and go with default setting of ansi nulls

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 12:42:46

I recommend that you use

IF @var IS NULL

rather than using SET ANSI_NULLS OFF

I used SET ANSI_NULLS OFF when I first started with SQL and then found various gotchas (which I now can't remember, sorry) and I then reverted to using IS NULL as a test instead.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 12:45:17

quote:
Originally posted by Kristen

I recommend that you use

IF @var IS NULL

rather than using SET ANSI_NULLS OFF

I used SET ANSI_NULLS OFF when I first started with SQL and then found various gotchas (which I now can't remember, sorry) and I then reverted to using IS NULL as a test instead.

Yeah..I also had such experiences that why i also recommend IS NULL

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 12:47:44

"I also had such experiences"

What? being so old that you cannot remember what you did when you first started using SQL?

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-02-05 : 12:53:01

Thanks Kristen and Visakh,

I agreed that changing setting will be problem in future(I am a plslq programme and oracle does not allows this)
however we are working on application which was using sybase as database and we wont be adding any extra code to it, just want this sybase code to work in sqlserver.

Hence I would like to change this at entire schema level. is it possible?

Thanks in advance.

Regards,
aak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 12:53:19

quote:
Originally posted by Kristen

"I also had such experiences"

What? being so old that you cannot remember what you did when you first started using SQL?

I really cant recollect it now

I remember tampering with ANSI_NULLS setting caused me quite a bit of problems once but dont exactly remember the details

Kristen
Test

22859 Posts

Posted - 2010-02-06 : 01:39:21

"just want this sybase code to work in sqlserver."

Microsoft built SQLServer using code that they licensed from Sybase (admittedly a little while ago now). I would be surprised if Sybase functions differently in this regard - i.e. that Sybase defaults to allowing "MyColumn = NULL" rather than "MyColumn IS NULL"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources