MS SQL 2008 returns null to string
I ended up with several stored procedures that use the TRY / CATCH statement, so I run the main program and if it generates any errors, I catch them. Now my problem is the catch output, 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 fine until the ContentName is NULL, then it works, I forgot that you need to pass the values to the string before you can add them to the nvarchar column. So how do I convert @ContentName before inserting it into the Errors table?
source to share
You don't have to throw - use the function coalesce
:
Returns the first non-null 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 an additional SQL Server offers notes cast
and convert
techniques that you can use to convert data from one type to another, you are not needed here, but it's good to know.
source to share
As an additional point to @ Andrew Hare's answer , I would format your lines 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')
by doing it this way, you can determine if the variable was an empty string or was null. Variables will have a value between double quotes, so "is an empty string," "is a single space, and null is null. Errors often occur with these three values.
source to share