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.
1条答案
按热度按时间4nkexdtk1#
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'tNULL
andTHROW
a generic error on the next line. I then know that the error in the outer batch occured on theERROR_LINE()-1
. Yuck, but it does work:Note that this will not work on errors that aren't "
CATCH
able", such as references to objects that don't exist.