T-SQL parameter always evaluates to NULL when it isn't NULL? 1

[1DMF]

Hi,

I am a bit confused why this..

	-- NBCS	
	IF @RAG IS NULL
		SET @SQL = 'UPDATE [Members].[Members_Live].[dbo].Business_Register SET Check_Flag = 9 WHERE Rec_ID = '+ CAST(@Case_ID AS varchar(15))
	ELSE
		SET @SQL = 'UPDATE [Members].[Members_Live].[dbo].Business_Register SET Check_Flag = ' + CAST(@RAG AS CHAR(1))+ ' WHERE Rec_ID = '+ CAST(@Case_ID AS varchar(15))

Is ALWAYS updating the Check_Flag column to 9 even though @RAG has a value?

I have code above this...

	-- Audit
	SET @SQL = 'UPDATE Compliance_Audit SET ' + @Col + ' = ' + CAST(@RAG AS char(1)) + ' WHERE Case_ID = ' + CAST(@Case_ID AS varchar(15))
	EXEC(@SQL)
		
	-- Checker
	SET @SQL = 'UPDATE [Members].[Members_Live].[dbo].Case_Checking SET ' + @Col + ' = ' + CAST(@RAG AS CHAR(1)) + ' WHERE Case_ID = ' + CAST(@Case_ID AS varchar(15))
	EXEC(@SQL)

The columns in those tables updates correctly to the value in @RAG, but the NULL check IF statement always says @RAG IS NULL when it isn't?

Why is this happening?

Thanks,
1DMF


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

It's not, found the culprit!

The VBA was triggering the SP twice once with the value and once with NULL, no wonder I couldn't work out why this was happening!

I think it's too hot for coding, my brain is frazzled!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[bborissov]

How about:

SET @SQL = 'UPDATE [Members].[Members_Live].[dbo].Business_Register SET Check_Flag = ' + CAST(ISNULL(@RAG, 9) AS CHAR(1))+ ' WHERE Rec_ID = '+ CAST(@Case_ID AS varchar(15))

Borislav Borissov
VFP9 SP2, SQL Server

 

[1DMF]

Yes, I did re-write it to use ISNULL(@ARG,Val_If_Null)

I tried several ways as I was thinking no syntaxical flavour was working, when it was and I was being an over heated, time to go home, idiot!

Thanks for the input, at least I know I was using the right syntax all along



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music