ERROR_LINE() always return 1 in dynamic query running on sql

Question

DECLARE @LineNumber INT,@QUERY VARCHAR(MAX)
BEGIN TRY
SET @QUERY='SELECT 1/0'
EXEC (@QUERY)
;THROW 50000, 'Line#', 1 -- all 3 values are arbitrary, but required
END TRY
BEGIN CATCH
    SET @LineNumber = ERROR_LINE()
END CATCH
SELECT @LineNumber

--the above code always returns 1 instead of 4 the error is occurring on line no 4

Answer 1

No 1 is correct. The dynamic SQL is not part of your stored procedure, but it is a (nameless) stored procedure of its own. And as Viorel suggests, error_line returns the line number inside the dynamic SQL.

Answer 2

Hi @Pushpendra

the above code always returns 1 instead of 4 the error is occurring on line no 4

You misunderstood where the error occurred.

How about assigning a correct query statement to the parameter @QUERY, for example,change SET @QUERY='SELECT 1/0' to SET @QUERY='SELECT 1'. You will get NULL, which shows that the entire framework of dynamic statements is free of syntax errors. The error occurs in the query statement you assigned. And of course, ERROR_LINE function returns the row value of the row in the statement you assigned the value where the error occurred.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

above statement ('SELECT 1/0') is just a example ,so if there are 10 dynamic query are running in a procedure so how will we find out that in which line no or query its giving error?

Any stored procedure that executes dynamic SQL should have a parameter

@debug bit = 0

And before each execution of dynamic SQL, you should have:

IF @debug = 1
   PRINT @sql

Answer 4

BEGIN TRY   
SET @MainQuery='Select 1/0';  
    SET @SQL = N'BEGIN TRY ' +  
               @MainQuery +                 
               N' END TRY BEGIN CATCH  SET @ErrorNumber = ERROR_NUMBER(); SET @ErrorSeverity = ERROR_SEVERITY();SET @ErrorState = ERROR_STATE();SET @ErrorLine = ERROR_LINE();SET @ErrorMessage = ERROR_MESSAGE(); END CATCH;';  
    EXEC 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 50000,'error',16;  
END TRY  
BEGIN CATCH  
    SET @LineNumber = ERROR_LINE()-1;
End Catch

---This will get you correct line number and also work for static and dynamic both --error