SQL print line number in comment of dynamically created stored procedure?

Question

I have written a script that is several thousands of lines long that I am using to generate some stored procedures dynamically.

I want to reference the script that generated the stored procedures in the comments in the stored procedures, and would like to be able to refer to the line in the script file by inserting the line number of the script file into the comments in the stored procedure file.

So for example if @@line_number gave the line number I want in the code bellow then @@line_number should be 5

1| declare @job varchar(max)
2| SET @job = '/* this is generated dynamicly by _______  */'
3| SET @job = @job + 'SELECT *' + CHAR(10)
4| SET @job = @job + 'FROM ' + @Table_Name + CHAR(10)
5| SET @job = @job + '/* ' + @@line_number + ' */'

Answer 1

You can use TRY / CATCH with a forced error as the CATCH block can return the line number that the error occurred on via the ERROR_LINE() function. The full construct, formatted for readability, is:

BEGIN TRY
    ;THROW 50000, 'Line#', 1 -- all 3 values are arbitrary, but required
END TRY
BEGIN CATCH
    SET @LineNumber = ERROR_LINE()
END CATCH

Now, to get the @LineNumber variable to populate with the line number that it is being set on, you can reduce that construct to a single line as follows:

BEGIN TRY;THROW 50000,'',1;END TRY BEGIN CATCH;SET @Line=ERROR_LINE();END CATCH

Here is a full example of it working:

SET ANSI_NULLS ON
SET NOCOUNT ON
GO
-- Line #1 (of current batch, not of the entire script if GOs are used)

DECLARE @CRLF NCHAR(2) = NCHAR(13) + NCHAR(10),
        @SQL1 NVARCHAR(MAX) = '',
        @SQL2 NVARCHAR(MAX) = '', -- Line #5
        @Line INT = -1 -- default to an invalid line #

SET @SQL1 += N'/********************' + @CRLF
SET @SQL1 += N' *' + @CRLF
SET @SQL1 += N' * Test Auto-' + @CRLF -- Line #10
SET @SQL1 += N' * Generated Proc 1' + @CRLF
BEGIN TRY;THROW 50000,'',1;END TRY BEGIN CATCH;SET @Line=ERROR_LINE();END CATCH
SET @SQL1 += N' * Line #:' + CONVERT(NVARCHAR(10), @Line) + @CRLF
SET @SQL1 += N' *' + @CRLF
SET @SQL1 += N' ********************/' + @CRLF -- Line #15

-- more code here

SET @SQL2 += N'/********************' + @CRLF
SET @SQL2 += N' *' + @CRLF -- Line #20
SET @SQL2 += N' * Test Auto-' + @CRLF
SET @SQL2 += N' * Generated Proc 2' + @CRLF
BEGIN TRY;THROW 50000,'',1;END TRY BEGIN CATCH;SET @Line=ERROR_LINE();END CATCH
SET @SQL2 += N' * Line #:' + CONVERT(NVARCHAR(10), @Line) + @CRLF
SET @SQL2 += N' *' + @CRLF -- Line #25
SET @SQL2 += N' ********************/' + @CRLF

PRINT @SQL1
PRINT @SQL2
GO

The line numbers returned for Proc 1 and Proc 2 are 12 and 23 respectively, which is correct for both.

Please note that the THROW command started in SQL Server 2012. If you are using SQL Server 2005, 2008, or 2008 R2, then you need to use RAISERROR() function instead of THROW.