CODEWITHSUNDEEP
sql-servererror-handlingtry-catch
SQL_Guy
SQL_Guy

Reputation: 353

Dynamic SQL executed inside a stored procedure in a TRY/CATCH block but the error is not returned

I have a simple stored procedure shown below. As you can see, the error is forcefully generated inside the dynamic SQL and passed to the CATCH block. The problem is that the details I expect are not returned. All I get back is this message:

Msg 50000, Level 16, State 1, Procedure myproc, Line 35 [Batch Start Line 2]

Why don't I get back the information as coded in the CATCH block - procedure name, error line and error message?

Here is my procedure:

CREATE PROCEDURE myproc
AS
    SET XACT_ABORT ON
    BEGIN TRY
        DECLARE @nSQL NVARCHAR(MAX) = 'SELECT 1/0'
        EXEC sp_executesql @nSQL
    END TRY
    BEGIN CATCH
        DECLARE @ERROR_NUMBER INT;
        DECLARE @ERROR_SEVERITY INT;
        DECLARE @ERROR_STATE INT;
        DECLARE @ERROR_PROCEDURE NVARCHAR(128);
        DECLARE @ERROR_LINE INT;
        DECLARE @ERROR_MESSAGE NVARCHAR(4000);
  
        SET @ERROR_NUMBER = ERROR_NUMBER();
        SET @ERROR_SEVERITY = ERROR_SEVERITY();

        SELECT @ERROR_STATE = CASE 
                                 WHEN ERROR_STATE() = 0 THEN 1
                                 ELSE ERROR_STATE()
                              END;
        SET @ERROR_PROCEDURE = ERROR_PROCEDURE();
        SET @ERROR_LINE = ERROR_LINE();
        SET @ERROR_MESSAGE = 'ERROR in ' + @ERROR_PROCEDURE + '; (ERROR Line: ' + CAST(@ERROR_LINE AS NVARCHAR(5)) + '); ' + 'ERROR MESSAGE: '
                            + ERROR_MESSAGE() + ';';

        RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE);
        RETURN (1);
    END CATCH;

Upvotes: 1

Views: 2177

Answers (3)

Amit11794
Amit11794

Reputation: 158

If you are using SQL Server 2012 or above version, it is recommended to use THROW.

Changing your whole CATCH block to below should be enough:

BEGIN CATCH
    THROW
END CATCH;

Msg 8134, Level 16, State 1, Line 1

Divide by zero error encountered.

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

"ERROR_PROCEDURE (Transact-SQL)":

ERROR_PROCEDURE returns NULL if the error did not occur within a stored procedure or trigger.

Since the dynamic SQL is not a procedure nor a trigger, this applies here and your @ERROR_PROCEDURE is NULL.

Now you use @ERROR_PROCEDURE in a string concatenation using + and assign that to @ERROR_MESSAGE. If you lookup the remarks in "+ (String Concatenation) (Transact-SQL)", you'll see that this can lead to the result of the concatenation being NULL and that likely happens in your case.

When you work with strings with a null value, the result of the concatenation depends on the session settings. Just like arithmetic operations that are performed on null values, when a null value is added to a known value the result is typically an unknown value, a string concatenation operation that is performed with a null value should also produce a null result. However, you can change this behavior by changing the setting of CONCAT_NULL_YIELDS_NULL for the current session.

So you pass a NULL as the first argument for RAISERROR and therefore no message is printed.

You can use concat() instead of + for the concatenation or replace NULLs manually.

But you should avoid RAISERROR anyway. See the note in "RAISERROR (Transact-SQL)".

The RAISERROR statement does not honor SET XACT_ABORT. New applications should use THROW instead of RAISERROR.

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46203

ERROR_PROCEDURE() is NULL because the error occurred in the inner scope of the dynamic SQL statement, not in the stored procedure itself. If you want the name of the current proc, use OBJECT_NAME(@@PROCID).

Upvotes: 1

Related Questions