Link to home
Start Free TrialLog in
Avatar of JC_Lives
JC_LivesFlag for United States of America

asked on

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

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?

Avatar of Rimvis
Flag of Lithuania image

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

Avatar of Aaron Shilo

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.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial