Why ERROR_LINE() returns 1 instead of exact line number in sql dynamic query execution

Question

I executes numbers of dynamic queries in my procedure so if any error occurred in those query I want to know on which line no error occurred, I always get wrong line no which is 1, which is incorrect.

I used ERROR_LINE() but it does not work with a dynamic SQL query.

DECLARE @LineNumber INT,@QUERY VARCHAR(MAX)

BEGIN TRY
SET @QUERY='SELECT 1/0'
EXEC (@QUERY)
;THROW 50000, 'Line#', 1
END TRY
BEGIN CATCH
    SET @LineNumber = ERROR_LINE()
END CATCH
SELECT @LineNumber

Here I want to know on which line no error occurred. The correct line no is 5 but SQL Server always shows 1 which is wrong.

Thanks in advance.

The line number returned is where the error occurred in the inner dynamic SQL batch (the single-line batch `SELECT 1/0`). — Dan Guzman, Jun 19 '23 at 13:10
It's because it return the line in the dynamic sql code, and you can't get the "real" line — siggemannen, Jun 19 '23 at 13:12
My parameters are dynamic that's why I have to use dynamic query — pushpendra, Jun 19 '23 at 13:44
*What* parameters, @pushpendra ? This is starting to sound like an [XY Problem](//xyproblem.info). — Thom A, Jun 19 '23 at 13:47
@ThomA, where did your answer go? I wanted to test it when i came home :) — siggemannen, Jun 19 '23 at 21:10
Fixed now, @siggemannen . I just hadn't had a chance to address it (it was a silly error too) and then forgot to come back. — Thom A, Jun 19 '23 at 21:20
If you are in the market for hacky solutions.... https://dbfiddle.uk/IU1UOnmW - though if your dynamic SQL itself is multiple lines things could get misleading. As it will treat the start of the dynamic SQL as though it started at line X instead of starting at Line 1 — Martin Smith, Jun 19 '23 at 21:53
simply I want to know on which line number error occurred if i use a dynamic query .SQL server returning 1 in case of dynamic query and working fine in case of static query — pushpendra, Jun 20 '23 at 05:25
(it was a silly error too) @Thom A , this is a example we have a lot number of errors you can use any one. — pushpendra, Jun 20 '23 at 05:28
That comment was aimed at siggemannen , @pushpendra . As you can see, I already posted an answer. — Thom A, Jun 20 '23 at 06:25

Thom A · Accepted Answer · 2023-06-20T15:07:07.413

This is a little "hacky" but it does provide the correct line number. I dump the code you have into a TRY...CATCH and then if an error is encountered I instead pass the error number and message back to the calling statement. Then I check if the value of @ErrorNumber isn't NULL and THROW a generic error on the next line. I then know that the error in the outer batch occured on the ERROR_LINE()-1. Yuck, but it does work:

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
        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;

Note that this will not work on errors that aren't "CATCHable", such as references to objects that don't exist.

Interesting that you say "partially", @pushpendra . What *didn't* work about it? Might be something I can cater for. — Thom A, Jun 20 '23 at 11:19
actually it will work when we use this as a procedure itself i want to make it more dynamic where whole query pass as a parameter and then shows error line in multiple queries simultaneously — pushpendra, Jun 20 '23 at 11:23
This seems like what you asked and what you needed to ask are not the same thing, @pushpendra . Perhaps, in the future, you should post a [new question](https://stackoverflow.com/questions/ask) with your *true* requirements; it seems you over simplified the problem. — Thom A, Jun 20 '23 at 11:24
Anyway a huge thanks to you, Without your help i am not able to do it @Thom A — pushpendra, Jun 20 '23 at 11:25

Why ERROR_LINE() returns 1 instead of exact line number in sql dynamic query execution

1 Answers1