Error Handling - Determine whether sp_executesql was the source

Question

When an error message is returned by sp_executesql, is there any built-in error handling method/mechanism that can be used to identify that this error was returned by this procedure vs. directly from the containing script?

In the case of the below, the error details identify the error as occurring on line 1 but they don't indicate that the line 1 being referred to is not line 1 from the script itself but rather line 1 in the query passed by the script to sp_executesql.

I'm looking for some way to identify the source so that I can log it accordingly. I'd like to log something like Script x - Call to inner query errored on that query's line 1 instead of the generic (and misleading) Script x errored on line 1.

Demo

-- do other things first

BEGIN TRY  
    EXECUTE sp_executesql @stmt = N'SELECT 1/0';
END TRY
BEGIN CATCH  
    SELECT ERROR_NUMBER() AS ErrorNumber
         ,ERROR_SEVERITY() AS ErrorSeverity
         ,ERROR_STATE() AS ErrorState
         ,ERROR_PROCEDURE() AS ErrorProcedure
         ,ERROR_LINE() AS ErrorLine
         ,ERROR_MESSAGE() AS ErrorMessage
END CATCH;

Returns:

ErrorNumber ErrorSeverity ErrorState  ErrorProcedure 
----------- ------------- ----------- ---------------
8134        16            1           NULL

Check out this thread: https://dba.stackexchange.com/questions/19895/how-to-log-error-details-when-using-using-try-catch-for-dynamic-sql-backup-comma — Jacob H, Apr 26 '19 at 16:50

score 3 · Answer 1 · answered Apr 27 '19 at 12:14

Unfortunately, the call stack isn't available with T-SQL error handling. Consider upvoting this feature request to facilitate capturing T-SQL stack details.

The example below uses a nested TRY/CATCH to raise a user-defined error (message number 50000) when the inner script errors, capturing the available details along with context description ("inner script"). When errors occur in the outer script, the original error is simply re-thrown. Absence of a context and a system error number indicates the outermost script erred, although you could build and raise a user-defined error there instead, including the outer script context description.

BEGIN TRY
    BEGIN TRY

        EXECUTE sp_executesql @stmt = N'SELECT 1/0;';
    END TRY
    BEGIN CATCH  

        DECLARE
             @ErrorNumber int
            ,@ErrorMessage nvarchar(2048)
            ,@ErrorSeverity int
            ,@ErrorState int
            ,@ErrorLine int;

        SELECT
             @ErrorNumber =ERROR_NUMBER()
            ,@ErrorMessage =ERROR_MESSAGE()
            ,@ErrorSeverity = ERROR_SEVERITY()
            ,@ErrorState =ERROR_STATE()
            ,@ErrorLine =ERROR_LINE();

        RAISERROR('Error %d caught in inner script at line %d: %s'
            ,@ErrorSeverity
            ,@ErrorState
            ,@ErrorNumber
            ,@ErrorLine
            ,@ErrorMessage);

    END CATCH; 
END TRY
BEGIN CATCH  

    THROW;

END CATCH; 
GO

score 0 · Answer 2 · answered Apr 26 '19 at 17:47

you can use sp_executeSQL's OUTPUT parameter:

DECLARE @ErrorLine NVARCHAR(32)
DECLARE @Params NVARCHAR(150) = '@Return INT OUTPUT'
DECLARE @SQL NVARCHAR(MAX) = ''


SET @SQL = @SQL + '     '
SET @SQL = @SQL + '    BEGIN TRY '
SET @SQL = @SQL + '        SELECT 100 AS First '
SET @SQL = @SQL + '        SELECT 1/0 AS Second '
SET @SQL = @SQL + '    END TRY '
SET @SQL = @SQL + '    BEGIN CATCH '
SET @SQL = @SQL + '        SELECT @Return = ERROR_LINE() '
SET @SQL = @SQL + '    END CATCH '
SET @SQL = @SQL + '     '

EXEC sp_executeSQL @SQL, @Params, @Return = @ErrorLine OUTPUT

SELECT @ErrorLine

this code will show @ErrorLine = 1 regardless of where the error is because technically, the entire SQL is on a single line and it makes the whole thing more complex, but you get the idea...

EDIT: if @ErrorLine is NULL, there's no error in sp_executeSQL.

CubeSpark · Answer 3 · 2021-02-24T22:19:40.810

Here is the solution that returns the line number. We are in a SPROC that takes parameters. Essentially, as a tSQL developer, you will need to guess where issues will occur, normally around arguments to formal parameter inputs.

-- Preamble
CREATE PROCEDURE [Meta].[ValidateTable]
@DatabaseNameInput VARCHAR(100), -- = 'DatabaseNameInput',
@TableNameInput VARCHAR(100), --  = 'TableNameInput',
@SchemaNameInput VARCHAR(100), --  = 'SchemaNameInput',
AS
BEGIN
DECLARE @crlf CHAR(2) = CHAR(13) + CHAR(10),

-----------Database Validity------------------
@IsDatabaseValid BIT,
@DatabaseNumber INTEGER,
@DatabaseNamePredicate NVARCHAR(100),
@CurrentExecutingContext NVARCHAR(40),
@DatabaseValidityExecutable NVARCHAR(100),
@DatabaseParameterString NVARCHAR(50),

-----------Table Validity------------------
@TableObjectIdentity INTEGER,
@TableString NVARCHAR(500),
@TableParameterString NVARCHAR(50),
@TableValidityExecutable NVARCHAR(200),

-----------Error Handling------------------
@ErrorState INTEGER = 0,
@ErrorNumber INTEGER = 0,
@ErrorSeverity INTEGER = 0,
@MyErrorMessage NVARCHAR(150),
@SetMessageText NVARCHAR(1024) = 'No Error Message Text for sys.messages.',
@ErrorDescription NVARCHAR(1024) = 'No error description was given.';

-- Be aware of SQL Injection Risk with no semi-colons at the line tails
SET @TableString = 'N' + '''' + @DatabaseNameInput  + '.' + @SchemaNameInput + '.' + @TableNameInput + '''';
SET @DatabaseParameterString = N'@DatabaseNumber INTEGER OUTPUT ';
SET @TableParameterString = N'@TableObjectIdentity INTEGER OUTPUT';

-- Phase 0.0, testing for database existence.
PRINT 'Table Validity Executable: ' + @TableValidityExecutable;
EXECUTE sp_executesql @DatabaseValidityExecutable, @DatabaseParameterString, @DatabaseNumber = @DatabaseNumber OUTPUT;

IF @DatabaseNumber IS NULL 
BEGIN 
  SET @MyErrorMessage = 'The @DatabaseNameInput parameter: "%s" specified by the caller does not exist on this SQL Server - ' + @@SERVERNAME;
  EXECUTE sys.sp_addmessage @msgnum = 59802, @severity = 16, @msgtext = @MyErrorMessage, @replace = 'replace', @lang = 'us_english';   
  RAISERROR(59802, 15, 1, @DatabaseNamePredicate);
END;

-- Phase 0.1, testing for table existence.
PRINT 'Table Validity Executable: ' + @TableValidityExecutable;
EXECUTE sp_executesql @TableValidityExecutable, @TableParameterString, @TableObjectIdentity = @TableObjectIdentity OUTPUT;
IF @TableObjectIdentity IS NULL 
BEGIN 
  SET @MyErrorMessage = 'The @TableNameInput parameter: "%s" specified by the caller does not exist in this database - ' + DB_NAME() +';';
  EXECUTE sys.sp_addmessage @msgnum = 59803, @severity = 16, @msgtext = @MyErrorMessage, @replace = 'replace', @lang = 'us_english';   
  RAISERROR(59803, 15, 1, @TableString);
END;

Error Handling - Determine whether sp_executesql was the source

Demo

3 Answers3