Stored procedures : LUSENET : SQL Server Database Administration : One Thread

In my stored procedure, inside the select statement, I want to check for null values in a field. if it's null i insert a value. if not null i keep the value. I'm getting errors on the code i have. Here's a copy of the line with the error below. any suggestions?

CASE When Null([ship-to code]) = TRUE THEN " " WHEN null([ship-to code]) = false then [ship-to code] end as SiteCode

-- Anonymous, December 03, 1999



Use the COALESCE function. This function returns the first nonnull expression among its arguments.

In your case, COALESCE([ship-to code], " ") is equivalent to this CASE function:


WHEN ([ship-to code] IS NOT NULL) THEN [ship-to code]

ELSE " "


Good Luck,


-- Anonymous, December 11, 1999


Coalesce allows any number of arguments and returns the first nonnull argument and null if all arguments are null. Isnull is a special case of coalesce that takes only two arguments. In your case you could use the isnull function like so:

isnull ([ship-to code], " ")

The original problem is that null is a value and not a function. So Null ([ship-to code]) will return a syntax error.

Hope this helps,


-- Anonymous, December 11, 1999

Moderation questions? read the FAQ