Sometime, SQL Server gives the different result in the ‘if’ condition for the same input.
I am given here the complete observation of IF Condition.
Those test results and possibilities given below.
‘Yes’ indicates ‘if’ condition pass.
‘No’ indicates ‘if’ condition fails.
Possibilities:
1.@var =null Sybase/SQL Server
Null=null -Yes/No -IS NULL
Val=null -No/No -IS NULL
Instead of ‘@var = NULL, we should give @var IS NULL.
2.@var <> null
Null<>null -No/No -IS NOT NULL
Val<>null -Yes/No -IS NOT NULL
Instead of ‘@var <> NULL, we should give @var IS NOT NULL.
3.@var =’constant’ (If the constant will be only Varchar/char)
1. Null =constant -No/No -ISNULL
2. Not null (Same value) =’constant’ -Yes/Yes -ISNULL
Instead of @var =’constant’, we should give ISNULL (@var,’’) =’constant’
1. Null<>constant -Yes/No -ISNULL
2. Value (Same value) <>constant -No/No -ISNULL
Instead of @var <> ’constant’, we should give ISNULL (@var,’’) <>’constant’
5. Constant=null -No/No -IS NULL
6. Constant<> null -Yes/No -IS NOT NULL
7.@var = or <> @var -Above six will cover this. So we need to add ISNULL.
CASE 1:
declare @variable varchar (30)
if (@variable <> '1')
select 'Yes'
else
select 'No'
Sybase output :’Yes’
Sql server output :’No’
After the Conversion from Sybase to SQL Server,at that time of Unit testing we should take care of this
issue.Changes can be like below.The substitute string in the ISNULL,cant be equal to the right side string.
How to resolve?:
declare @variable varchar (30)
if (ISNULL(@variable,'') <> '1')
select 'Yes'
else
select 'No'
The output will be ‘Yes’.
CASE 2:
declare @variable int
if (@variable <> '1')
select 'Yes'
else
select 'No'
Sybase output :Implicit conversion from data type ‘varchar’ to ‘ int’ is not allowed.
Sql server Output :’No’
How to resolve?:
declare @ variable int
if (@variable <> 1)
select 'Yes'
else
select 'No'
The code will get executed without error and gives ‘Yes’ as output.
This scenario, wont make any issue.Because from Sybase itself raise the error,if the data type is different.
Please don’t add isnull() blindly for all the comparision.
CASE 3:
declare @variable varchar(30)
if (@variable <> null)
select 'Yes'
else
select 'No'
Sybase output :’No’
Sql server output :’No’
How to resolve?:
declare @variable varchar(30)
if (@variable is not null)
select 'Yes'
else
select 'No'
The output for the above code is ‘No’.But we should change as ANSII standard.
CASE 4:
declare @variable varchar(30)
if (@variable = null)
select 'Yes'
else
select 'No'
Sybase output :’Yes’
Sql server output :’No’
How to resolve?:
declare @ variable varchar(30)
if (@variable is null)
select 'Yes'
else
select 'No'
The output for the above code is ‘Yes’.But we should change as ANSII standard.
CASE 5:
declare @variable1 varchar(30)
declare @variable2 varchar(30)
if (@variable1 = @variable2)
select 'Yes'
else
select 'No'
Sybase output :The output for the condition is based on the different input for the variables.
We can predict the output based on the above four Cases.
Sql server output :Output will be based on the above four cases.So we should add the ISNULL in both the variables.
How to resolve?:
declare @variable1 varchar(30)
declare @variable2 varchar(30)
if (ISNULL(@variable1,'') = ISNULL(@variable2,''))
select 'Yes'
else
select 'No'
The output will be based on the above four cases.If we add the ISNULL In the variable,we can avoid the wrong output.
Initially I thought sybase will behave same manner in both, if condition and where clause for the 2 exception scenario’s given below.Finally I realised that,execution and output generation for the ‘if condition’ and ‘where clause will be different.
In case of where clause,sybase executing same as like sql serverJJ.Here I am meaning that,both are neglecting null value comparision in the table where clause.Please view the detailed explanation below.
The above given first and fifth case is applicable varchar data type.If we use the integer variable,which will create the functionality issue.The comparision input is string variable,we should give the alternate value for the ISNULL function.If the input is integer data type,please avoid for changing the code with isnull().Adding isnull() for all if statement will leads to headache.
Table level Where clause behaviour
Sybase:
Case 1 for where clause:
If we execute the below select statement,
select * from dbo.table where column1 (null value)=column2(null value)
and column3=50
I am getting 0 record,though there was a record for column3=50 and both the columns column1, column2 has null value.Which means sybase not comparing the null value in where clause.
Case 2 for where clause:
If we execute the below select statement,
select * from dbo.table where column1(some value)<> column2 (null value)
and column3 =50
I am getting 0 record,though there was a record for column3 =50 .One more is column column2 has null value and column1 has not null value.Which means means sybase not comparing the null value in where clause.
Sql server:
Case 1 for where clause:
If we execute the below select statement,
select * from dbo.table where coloumn1 (null value)=column2(null value)
and column3=50
I am getting 0 record,though there was a record for column3 =50 and both the columns column1, column2 has null value.Which means sql server not comparing the null value in where clause.
Case 2 for where clause:
If we execute the below select statement,
select * from dbo.table where column1(some value)<>column2(null value)
and column3=50
I am getting 0 record,though there was a record for column3=50 .One more is, column column2 has null value and column1 has not null value.Which means sql server not comparing null values in where clause.
Conclusion:
We can neglect the ‘where’ clause comparision.We don’t need to modify in where
clause.Because Sybase and SQL Server are neglecting records which is having null comparision
in where clause.But we should take care for the ‘If condition’ for four different Cases given above.
That is Case1,Case 3,Case 4,Case 5.
Please click here to know about sybase to sql server migration issues
in where clause.But we should take care for the ‘If condition’ for four different Cases given above.
That is Case1,Case 3,Case 4,Case 5.
Please click here to know about sybase to sql server migration issues
No comments:
Post a Comment