SET ANSI NULLS - 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

SQL Server Development (2000)

SET ANSI NULLS

Author

Topic

mobasha
Starting Member

45 Posts

Posted - 2007-05-20 : 02:22:40

Hi every on am trying to use SET ANSI NULLS ON/OFF from inside a stored procedure but I don’t see the result but when I try it alone with a select statement it is working ,now is this option work from inside a sp or not??????
And please to tell me what is in the help or books on line I spend enough time there……

MobashA

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-20 : 09:31:55

ca you show us exactly how are you using it?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

mobasha
Starting Member

45 Posts

Posted - 2007-05-20 : 09:50:25

create proc test(@us varchar(30))
as
begin
SET ANSI_NULLS OFF
--update statement
update emp set dname='loko' where location=@us
--insert--delete
end

in this case with out the ansi nulls set to off if @us=null and the location=null
then the update statement will never happen i need it when comparing null to null get true ,
or if i want to update all the deptartement that thier location=null>>>
i know that there r many ather ways but i need this one ..

MobashA

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-20 : 10:07:06

you have to put set ansi null outside the procedure

SET ANSI_NULLS OFF

exec test null

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-20 : 11:51:46

You have to set options before procedure declaration statement like this:

SET ANSI_NULLS OFF
GO

create proc test(@us varchar(30))
as
begin 
--update statement
update emp set dname='loko' where location=@us
--insert--delete
end

SET ANSI_NULLS ON
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mobasha
Starting Member

45 Posts

Posted - 2007-05-21 : 05:32:00

i did so it is not working

MobashA

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-21 : 05:43:05

can you show us the fixed sproc?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

mobasha
Starting Member

45 Posts

Posted - 2007-05-21 : 06:12:33

sory i cant put it online "company rules" but its britty much like the one i but it above
,sory to make things hard for u but i did try many things like this to set an option inside a stored procedure and try to uesd it there but the procedure like it never see it and no error generated,i think it is s.th like i have read "microsoft said it can be uesd and they were worng in this like many other things"

MobashA

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-21 : 08:01:29

"i did try many things like this to set an option inside a stored procedure"

Don't set the option inside proc. Do it either the way spirit1 suggested or try my suggestion.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mobasha
Starting Member

45 Posts

Posted - 2007-05-22 : 02:08:16

ok thanks alot for help

MobashA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources