Getting the line sys.sp_executesql was executed on during an error

This is a problem I encountered recently on a question on Stack Overflow but found it interesting enough that I wanted to also share it here.

When you are using dynamic SQL, you may have noticed that when an error occurs the line number provided in the error is that of the query in the dynamic batch, not the outer query. Take for example the following example:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'DECLARE @name sysname;' + @CRLF +
           N'SELECT @name = name' + @CRLF + 
           N'FROM sys.tables' + @CRLF + 
           N'WHERE object_id = ''abc'';';

EXEC sys.sp_executesql @SQL;

The error returned from this query is the following:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'abc' to data type int.

Notice that the error line states line 2 (the line that errored in the dynamic batch) not line 8, which is the line the sys.sp_executesql was called on. Knowing the line the error occured on within the dynamic batch is certainly important, but if you're working with multiple dynamic batches you have no way of knowing which dynamic batch might have produced the error; was it the one executed on line 100? Line 200? Line 350?

Solving this problem wasn't wasn't exactly simple and it came with a couple of caveats.

Firstly, the entire script (that would be executing the dynamic batch) needs to go in a TRY...CATCH, as this enables you to control what error to THROW. Next I also had to put the batch inside the dynamic batch inside a TRY..CATCH. The reason for this is that when an error occurs inside that batch I don't want it to be thrown, but instead I want to capture that error's details. I do this by passing parameters (which would be in addition to any you already have) to sys.sp_executesql to capture things like the value of ERROR_MESSAGE() and ERROR_LINE().So the assignment and execution of your @SQL (like) parameter might look something like this:

SET @SQL = N'BEGIN TRY' + @CRLF +
           N'    SELECT 1/0;' + @CRLF +
           N'END TRY' + @CRLF +
           N'BEGIN CATCH' + @CRLF +
           N'    SET @ErrorNumber = ERROR_NUMBER();' + @CRLF +
           N'    SET @ErrorSeverity = ERROR_SEVERITY();' + @CRLF +
           N'    SET @ErrorState = ERROR_STATE();' + @CRLF +
           N'    SET @ErrorLine = ERROR_LINE();' + @CRLF +
           N'    SET @ErrorMessage = ERROR_MESSAGE();' + @CRLF +
           N'END CATCH;';
EXEC sys.sp_executesql @SQL, N'@ErrorNumber int OUTPUT, @ErrorSeverity int OUTPUT, @ErrorState int OUTPUT, @ErrorLine bigint OUTPUT, @ErrorMessage nvarchar(4000) OUTPUT', @ErrorNumber OUTPUT, @ErrorSeverity OUTPUT, @ErrorState OUTPUT, @ErrorLine OUTPUT, @ErrorMessage OUTPUT;

Now we know that the dynamic batch has errored, as the variable like @ErrorNumber will have a non-NULL but the actual error has not been thrown; the outer batch will continue. This enables us to intentionally THROW an error on the next line if any of those variables aren't NULL:

IF @ErrorNumber IS NOT NULL THROW 100000,'Dynamic Batch Error',16;

The advantage of throwing our own error is that we have forced an error to occur on the line after the call to sys.sp_executesql, so the value of ERROR_LINE() in the outer batch is now the line number of sys.sp_executesql +1. Note that is important that the IF is all on one line here (though you can use multiple, you just need to remember how many lines you use "up").

With that in mind we can then generate our own error message in the CATCH that provides details of where the error occurred in the outer batch, and include the error from the dynamic batch. I used something like this to create the error:

SET @LineNumber = ERROR_LINE()-1;
SET @ErrorNumber = 100000 + @ErrorNumber;
SET @ErrorMessage = CONCAT(N'An error was encountered in a dynamic batch executed on Line ', @LineNumber, N'.',@CRLF,
                           N'    ', N'Msg ', @ErrorNumber-100000, N', Level ', @ErrorSeverity, N', State ', @ErrorState, N', Line ', @ErrorLine,
                           @CRLF, N'    ', @ErrorMessage);

I did try using FORMATMESSAGE, however, SQL Server was returning terse mode errors, so I gave up on that.

You can see I use ERROR_LINE()-1. Note that this does make the assumption that your call to sys.sp_executesql is on a single line; this is the first caveat. If you have multiple calls to sys.sp_executesql and they are multi-line calls then you may prefer to state that the error occurred prior to ERROR_LINE(), as that's more "dynamic". The use of @ErrorNumber+100000 is intentional as well; if this is being passed to an application layer then you know that error 108134 is error 8134 "under the hood". You don't have to use 100000, but it just felt appropriate.

Once you've designed the error message you want, you can then THROW it:

THROW @ErrorNumber, @ErrorMessage, @ErrorSeverity;

Our second caveat, however, is that this will not handle error that occurs in the dynamic batch due to compilation errors (and other non-CATCHable errors), such as when a reference to an object that doesn't exist occurs. It also would cause an ambiguous error to be thrown if an error occurs outside of the dynamic batch. As such you'll also need to check the value of @ErrorNumber in the CATCH and if it's NULL just THROW the error. This, unfortunately, won't help you with some errors, but ideally you should also have other processes in place to help you with that, such as optional PRINT statement to provide the SQL being executed when debugging the query.

All in all, this gives you a batch that looks something like this:

DECLARE @LineNumber int,
        @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10),
        @ErrorNumber int,
        @ErrorSeverity int,
        @ErrorState int,
        @ErrorLine bigint,
        @ErrorMessage nvarchar(4000);

BEGIN TRY
    SET @SQL = N'BEGIN TRY' + @CRLF +
               N'    SELECT 1/0;' + @CRLF +
               N'END TRY' + @CRLF +
               N'BEGIN CATCH' + @CRLF +
               N'    SET @ErrorNumber = ERROR_NUMBER();' + @CRLF +
               N'    SET @ErrorSeverity = ERROR_SEVERITY();' + @CRLF +
               N'    SET @ErrorState = ERROR_STATE();' + @CRLF +
               N'    SET @ErrorLine = ERROR_LINE();' + @CRLF +
               N'    SET @ErrorMessage = ERROR_MESSAGE();' + @CRLF +
               N'END CATCH;';
    EXEC sys.sp_executesql @SQL, N'@ErrorNumber int OUTPUT, @ErrorSeverity int OUTPUT, @ErrorState int OUTPUT, @ErrorLine bigint OUTPUT, @ErrorMessage nvarchar(4000) OUTPUT', @ErrorNumber OUTPUT, @ErrorSeverity OUTPUT, @ErrorState OUTPUT, @ErrorLine OUTPUT, @ErrorMessage OUTPUT;
    IF @ErrorNumber IS NOT NULL THROW 100000,'error',16;
END TRY
BEGIN CATCH
    IF @ErrorNumber IS NULL --Error occured outside a dynamic batch
        THROW;
    SET @LineNumber = ERROR_LINE()-1;
    --SELECT @LineNumber, @ErrorMessage;
    SET @ErrorNumber = 100000 + @ErrorNumber;
    SET @ErrorMessage = CONCAT(N'An error was encountered in a dynamic batch executed on Line ', @LineNumber, N'.',@CRLF, N'    ', N'Msg ', @ErrorNumber-100000, N', Level ', @ErrorSeverity, N', State ', @ErrorState, N', Line ', @ErrorLine, @CRLF, N'    ', @ErrorMessage);
    THROW @ErrorNumber, @ErrorMessage, @ErrorSeverity;
END CATCH;

This ends up with the following error:

Msg 108134, Level 16, State 16, Line 32
An error was encountered in a dynamic batch executed on Line 22.
    Msg 8134, Level 16, State 1, Line 2
    Divide by zero error encountered.

I fully admit this isn't the most "graceful" or intuitive solution, and you're free to change this for your needs, but hopefully if you do find you have the need for something like this it gives you enough of an idea to achieve the goal you need.