| Author |
Topic |
|
manikandan
Starting Member
35 Posts |
 Posted - 2002-03-26 : 22:31:23
|
| Declare @temp1 as intselect @temp1 = NULLIf @temp1 <> 5 thenprint 'Success'If i run this query, the control is not going into the then part. IF you could help me out on this, it will be great.cheersmanikandan |
|
|
marileng
Starting Member
28 Posts |
Posted - 2002-03-26 : 22:51:44
|
| try thisDeclare @temp1 as int select @temp1 = null If (@temp1 <> 5 or @temp1 is null) print 'Success' |
 |
|
|
manikandan
Starting Member
35 Posts |
Posted - 2002-03-26 : 23:20:09
|
| HiHi marileng,thanks for the reply. I too did that...But the problem is that we are migrating somewhere more than 1000 procs from sybase to sql server. And in sybase it works. Is there any specific reason on why it is not working in sql server. or is there any shortcuts to rectify the problem.( Any set option).If there is not any, we have to do this. cheersmanikandan |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-03-26 : 23:29:29
|
| The problem here is the way your database thinks of NULL.NULL is NULL it is a Non-Value. NULL is not meant to equal anything. If you SELECT @i = NULL, then @i ceases to have a value. So nothing should ever be equal to NULL, even NULL isn't EQUAL to NULL.There is a fix to this, you can SET ANSI_NULLS = ON and then NULL can equal things, but this is not correct. Some people here will lecture you for days on why this is a BAD THING. I'm not going to do that, even though I agree with them.This will give you a quick fix, but will always cause you problems unless you do it right. Read the parts of books online referring to SET ANSI_NULLS for some more info.Damian |
|
|
|
manikandan
Starting Member
35 Posts |
Posted - 2002-03-26 : 23:49:51
|
| Hithanks. i will go and modify in all the places. (sybase in this case, not sql 92 compliant ...i don't know)I thought of setting ansi_nulls off at the start of stored procedure and changing the setting again at the end of the same. But alas, that could not be done it seems. (it takes the setting from what it was at the creation time.)cheersmanikandanCheersManikandan |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-03-27 : 00:09:29
|
I shouldn't be telling you this.... You can change the setting for the whole database using the sp_dboption stored procedure.But I think you are doing the right thing by changing your code.Damian |
|
|
|
manikandan
Starting Member
35 Posts |
Posted - 2002-03-27 : 00:34:50
|
| Hai,i saw sp_dboption. Itz really an easy way to do it. but we are not allowed here to have that setting. So, we have started converting the code. ( i am not the one who is actually doing the conversion. ( more than 1000 procedures) So i could sit and relax).cheersmanikandan |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-03-27 : 00:38:24
|
quote:
i am not the one who is actually doing the conversion. ( more than 1000 procedures) So i could sit and relax
HA HA HA HAThat sounds like a good plan to me  Damian |
|
|
|
|
Problems with null value