CODEWITHSUNDEEP
sql-serverexceptiontry-catchdynamic-sqlsp-executesql
pushpendra
pushpendra

Reputation: 3

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

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.

Upvotes: 0

Views: 618

Answers (1)

Thom A
Thom A

Reputation: 95588

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

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

Upvotes: 1

Related Questions