July 24, 2019 at 9:02 pm
Hello experts,
I have tested this out a bit as follows:
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT ERROR_LINE() AS ErrorLine;
END CATCH;
GO
Ref.: https://docs.microsoft.com/en-us/sql/t-sql/functions/error-line-transact-sql?view=sql-server-2017
I understand the result - line 3
ErrorLine
3
However, I'm trying to pinpoint the error line reported in a stored procedure. When I script out the stored procedure, as you all know, I get text like this before the main procedure code:
USE [MyDB]
GO
/****** Object: StoredProcedure [dbo].[MyProc] Script Date: 7/24/2019 4:47:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MyProc]
AS
BEGIN
BEGIN TRY
...
I'm just wondering when ERROR_LINE () starts counting:
In the example from the above reference I created the sample procedure and got ErrorLine of 5, fyi.
Which I guess means ERROR_LINE () counts from '-- Create a stored procedure that'?
-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'usp_ExampleProc', 'P' ) IS NOT NULL
DROP PROCEDURE usp_ExampleProc;
GO
-- Create a stored procedure that
-- generates a divide-by-zero error.
CREATE PROCEDURE usp_ExampleProc
AS
SELECT 1/0;
GO
BEGIN TRY
-- Execute the stored procedure inside the TRY block.
EXECUTE usp_ExampleProc;
END TRY
BEGIN CATCH
SELECT ERROR_LINE() AS ErrorLine;
END CATCH;
GO
Thanks for any help!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 24, 2019 at 10:11 pm
My testing agrees with yours ... line 1 appears to be the line just before CREATE PROC
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 24, 2019 at 10:46 pm
It can depend but it's a fun one to play with. USE gets ignored but you want to test it with no line feeds after the set options as well as multiple lines feeds after the options to see the difference. So test it this way:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MyProc]
AS
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT ERROR_LINE() AS ErrorLine;
END CATCH;
And then test it this way:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MyProc]
AS
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT ERROR_LINE() AS ErrorLine;
END CATCH;
And then you can reset the line number itself with LINENO 0 (that's a zero). So change it this way and then test it:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MyProc]
AS
LINENO 0
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT ERROR_LINE() AS ErrorLine;
END CATCH;
Sue
July 25, 2019 at 5:06 pm
Thanks for the feedback, Phil and Sue!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 25, 2019 at 5:28 pm
If I need to know the location, I usually set a variable with a location id that is unique.
DECLARE @code_location varchar(20)
SET @code_location = '1000-SELECT'
BEGIN TRY
...
END TRY
BEGIN CATCH
SELECT @code_location AS code_location, ERROR_MESSAGE() AS error_message
...
END CATCH
SET @code_location = '1020'
...
etc
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply