How can I return the line number within a sql script?

JC_Lives
JC_Lives used Ask the Experts™
on
Hi experts,

When running a SQL script, I often print the time and a message to indicate how far I've gotten so far in the exection. Is there a function that can print the line number on which the statement is being executed?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi JC_Lives,

No, I'm not aware of such function. But you can place RAISERROR statements in key places to get messages during execution.


RAISERROR ('Batch starting',0,1) WITH NOWAIT

RAISERROR ('Now selecting some data',0,1) WITH NOWAIT
SELECT 'Some data',0, GETDATE()
RAISERROR ('Some data selected',0,1) WITH NOWAIT

RAISERROR ('Now selecting some more',0,1) WITH NOWAIT
SELECT 'Some more',1, GETDATE()
RAISERROR ('Some more data selected',0,1) WITH NOWAIT

RAISERROR ('Batch completed',0,1) WITH NOWAIT

Open in new window

Aaron ShiloChief Database Architect

Commented:
hi

what you want to achive depends on what your doing.

you can use the @@rowcount LIKE so :

SELECT @Msg = CAST(@@ROWCOUNT AS VARCHAR(10)) + ' rows affected'

Open in new window


but only if your batch operation works on bulks of data.
if you process the entire workload as a hole than you will only be able to see the end of the line.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
I don't know of any direct way to do that.

You could force an error and then use the ERROR_LINE() function to get the line number of the error, then sutract 1 to get to the line before that you really wanted to identity.

For example:


DECLARE @line# int
-- place in code where you want the line #
BEGIN TRY
SELECT 1/0 --force error to make "catch" code run
END TRY
BEGIN CATCH
SELECT @line# = ERROR_LINE ()
END CATCH
SELECT @line# = @line# - 1
SELECT @line#

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial