ISNUll problem. Where clause doesn't work properly when I use isnull clause
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off? Thank you
setansi_nullsoff
go
declare
@inFileName VARCHAR(100),
@inFileSizeINT,
@Idint,
@inlanguageidINT,
@inFileVersion VARCHAR(100),
@ExeStateint
set @inFileName='A0006337.EXE'set @inFileSize= 28796set @Id= 1set @inlanguageid=nullset @inFileVersion=NULLset @ExeState=0select
Dr.StateIDfromtable1 dRwhere
DR.[FileName]= @inFileName
AND DR.FileSize=@inFileSizeAND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
AND DR.languageid=isnull(@inlanguageid,null)AND DR.[ID]= @ID)go
set
ansi_nullson