MS SQL 2008 cast null to string

Question

Hello!

I got a few stored procedures that uses a TRY/CATCH statement, so i execute the main program and if it generates any errors i catch them. Now my problem is in the catch statement, i have this piece of code:

            BEGIN TRY
  INSERT INTO ContentTypes (ContentName, ContentPath) VALUES (@ContentName, @ContentPath)

  SET @QResult = 0
 END TRY
 BEGIN CATCH   

  SET @QResult = 1
  INSERT INTO Errors (ErrorNumber, ErrorLine, ErrorProcedure, ErrorSeverity, ErrorState, ErrorParameters)
  VALUES (ERROR_NUMBER(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_SEVERITY(), ERROR_STATE(), 'ContentName:' + @ContentName + ',ContentPath:' + @ContentPath)
  RETURN
 END CATCH

This works perfectly until ContentName is NULL then it crashes, i forgot that you need to cast the values to string before you can add them to a nvarchar column. So how do i convert @ContentName before i insert it into the Errors table?

Answer 1

You don't need to cast - use the coalesce function:

Returns the first nonnull expression among its arguments.

You would use it like this:

insert into Errors (ErrorNumber, ErrorLine, 
    ErrorProcedure, ErrorSeverity, ErrorState, ErrorParameters)
values (ERROR_NUMBER(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_SEVERITY(), 
    ERROR_STATE(), 'ContentName:' 
    + coalesce(@ContentName, '')
    + ',ContentPath:' + coalesce(@ContentPath, ''))

As a side note, SQL server offers cast and convert methods that you can use to convert data from one type to another. You don't need it here but it is good to know.

Answer 2

as an extra point to @Andrew Hare's answer, I would format your strings a little differently:

insert into Errors (ErrorNumber, ErrorLine, 
    ErrorProcedure, ErrorSeverity, ErrorState, ErrorParameters)
values (ERROR_NUMBER(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_SEVERITY(), 
    ERROR_STATE()
    ,'ContentName:' + coalesce('"'+@ContentName+'"', 'null') 
    + ',ContentPath:' + coalesce('"'+@ContentPath+'"', 'null')

doing it this way, you can tell if the variable was an empty string or was null. The variables will have their value between double quotes, so "" is an empty string, and " " is a single space and null would be null. Bugs crop up quite often with these three values.