Null handling oddity

thrakazog

I was working on a chunk of SQL in management studio. Finally got it to return the results I wanted. Then I converted it to a stored procedure, where it promptly stopped working. Upon tracking the bug down I found that SQL was handling Null variables differently in dynamic SQL vs my stored procedure. I had this bit of code:

if (@Replace != '')
set @MaterialNumber = @Replace

In dynamic SQL if @Replace was null, we did not enter the if block. In a stored procedure when @replace was null we entered the if block and set @MaterialNumber to null. :wtf: To the best of my knowledge, this should never happen. Is this a bug in SQL Server or is there a setting hidden somewhere that could account for this difference?

Kill some time, play my game Hop Cheops[^]

Chris Meech

Whenever I check for NULL, I avoid doing so with comparison operators. There should be some IS NULL check available to use instead. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

PIEBALDconsult

That seems correct to me, but I'm unsure what you mean by "dynamic SQL".

Jorgen Andersson

In SQL-server '' is not the same as null, it's a zero length string.

Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions

Corporal Agarn

May not be the best way but try:

IF (ISNULL(@Replace, '') != '')
SET @MaterialNumber = @Replace;

thrakazog

My concern wasn't so much the null handling, that's easy enough to work around. I'm more concerned by the fact that the SQL code behaved differently when it was run in a stored procedure VS when it was run directly in Enterprise Manager. This is the first instance I've discovered where there was a difference.

Kill some time, play my game Hop Cheops[^]

thrakazog

By dynamic I meant SQL run directly in Enterprise Manager. When run in a stored procedure the SQL code behaved one way, when run directly in Enterprise Manager it behaved another way. This is the first instance I've discovered where there was a difference between the two.

Kill some time, play my game Hop Cheops[^]

jschell

thrakazog wrote:

In dynamic SQL if @Replace was null, we did not enter the if block.

Create a stored procedure that demonstrates exactly that and then post it.

thrakazog

Gaaaah, I finally found the cause of the problem. I apparently created my original sproc with "SET ANSI_NULLS OFF". By default queries in management studio runs with ANSI_NULLS ON. DOH![^] For a simple example of the sproc I was playing with:

create PROCEDURE [dbo].[Bob_Test]
@MaterialNumber varchar(18)
AS

DECLARE @Replace varchar(18)

SELECT @MaterialNumber

IF (@Replace != '')
SET @MaterialNumber = @Replace

SELECT @MaterialNumber

When I call this with ANSI_NULLS OFF

Bob_Test '123456'

my results show 123456 from the first select in the sproc, and NULL for the select after the if statement. With ANSI_NULLS ON both selects return 123456. My faith SQL Server has been restored. Tune in next week for more inept coding... :cool:

Kill some time, play my game Hop Cheops[^]