nullif error when no value is specified

[n36z]

I am testing a parameter for a Null value.

When I specify a "1" for the value, the expression works correctly:

declare @value1 int
set @value1=1

select nullif(@value1,'')


Without specify a value, I get an error message that reads "Incorrect syntax near the keyword 'select'.":

declare @value1 int
set @value1=

select nullif(@value1,'')

What do I need to do?

 

[AndyInNC]

Try IsNull instead.

SELECT IsNull(@value1, 0)

And under certain circumstances, SQL will bark at you when confusing data types like your example. @value1 was declared as int, but you returned a string. You may need to Convert() it. (Just a caution.)

 

[n36z]

Hi Andy, the same error was returned. This happens when no value is set as indicated in the expression. Could the error be that some value has to be specified in a set command and not an error with the isNull statement itself?

declare @value1 int
set @value1=

select isNull(@value1, 0)

 

[bborissov]

The error comes from this line:

set @value1=

If you want the value to be NULL, just comment this line.

declare @value1 int
SELECT  @value1

You can't have a SET operator w/o actually to store some value to the variable.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[AndyInNC]

Sorry, I misunderstood. I didn't take the "SET @value1 =" literally.

Yes, there has to be something after the equals. Otherwise it's an incomplete statement. If you want it to be NULL, that works. "SET @value1 = NULL", but that is like it was never set in the first place.

 

[gmmastros]

The problem is... you have set and select, and it's not valid syntax.

If you want to see how NullIf works when the value is null, you need to set it to NULL, or remove the SET altogether (because local variables default to NULL when they are created).

Ex:

declare @value1 int
set @value1=NULL

select nullif(@value1,'')

or

declare @value1 int

select nullif(@value1,'')

Now... looking at your code, I notice a bit of a problem. You are passing @value1 in to the first parameter, but you have an empty string in the 2nd parameter. NullIf will return NULL if the first parameter matches the 2nd parameter. Therefore, it's best to make sure both parameters have the same data type. SQL will do an implicit data conversion for you, but it's best to avoid that whenever you can because it can have unexpected behavior. For example, what would you expect from this:

declare @value1 int
Set @Value1 = 0

select nullif(@value1,'')

In this case, 0 is not the same thing as empty string, but... SQL returns NULL because it implicitly converts the string to match the data type of the first argument, and an empty string converts to 0, so the arguments match and NULL is returned.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[n36z]

Thank you all, the solutions worked.