Equivalent of Iif() or Nz() in SQL Server? 1

[ESquared]

The simple question:

Is there an easy way to convert a Null to another value inline?

If I could use Nz (which is specific to Access only) it would look something like this:

SET @DebugMessage = @DebugMessage + CAST(Nz(@CurrentStatus,'Null') AS varchar(5))

So the string 'Null' is added to the message instead of getting an error on trying to convert a Null to varchar.

I appreciate anyone's help!

-E²




Non-obligatory reading to follow. A man's got to have the opportunity to complain, *somewhere*.

I just spent a few hours beating my brains out trying to understand the misbehavior of my stored procedure and finally realized the problem: Nulls.

It took me half the time to realize that gee, since Nulls propagate, if I concatenate a string to a Null I'll still have Null! Brilliant.

It took me the other half of the time to find out there is a debugger, get it going (sort of), receive the error message "[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification" but not be able to step through code or be told a line number, and finally nail this down to lines where I was doing "CAST(@MyValue AS varchar(6))" when @MyValue was Null.

It was so confusing because I never received an error message in the Results pane, the stored procedure just up and quit, leaving me to discover this only by inspecting results and finding them missing...

I just realized I could write my own function to do this. Hm.

 

[TimKThailand]

Try

SET @DebugMessage = @DebugMessage + CAST(IsNull(@CurrentStatus,'Null') AS varchar(5))

 

[ESquared]

aha, IsNull. Thank you very much! I only started learning stored procedures last week and SQL Server-specific syntax (as opposed to Access) a month ago. Sorry to waste your time, I DID search the help files and online.

I was tired of writing CAST... AS so I made a function:

[tt]IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'CStr')
DROP FUNCTION CStr
GO


CREATE FUNCTION Cstr
(@TheValue sql_variant)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @ReturnVal varchar(8000)
IF @TheValue IS NULL
Set @ReturnVal = 'Null'
ELSE
Set @ReturnVal = RTRIM(CAST(@TheValue AS varchar(8000)))
RETURN @ReturnVal
END
GO

DECLARE @MyNumber int
SET @MyNumber = 12348758
PRINT dbo.CStr(@MyNumber)

GO[/tt]

This is working nicely for me. The only thing is that I have to use the syntax "dbo.CStr" because it complains about not being able to find the CStr function when I leave off the dbo prefix.

 

[ESquared]

[tt]CREATE FUNCTION Cstr
(@TheValue sql_variant)
RETURNS varchar(8000)
AS
RETURN RTRIM(CAST(ISNULL(@TheValue,'Null') AS VarChar(8000)))[/tt]

 

[ESquared]

CREATE FUNCTION Cstr
(@TheValue sql_variant)
RETURNS varchar(8000)
AS
BEGIN
RETURN RTRIM(CAST(ISNULL(@TheValue,'Null') AS VarChar(8000)))
END

GO