Avatar of JC_Lives
JC_Lives
Flag 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?

Thanks!
Microsoft DevelopmentMicrosoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
Rimvis

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 Shilo

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.
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck