Using the @@ProcID Function

It's not glamorous, but it's occasionally useful when you're writing logging code to know the name or ID of the stored procedure that is executing. You can use @@ProcID to just get the ID, or with a little more work we can get the name, either way it's better than having logging code that embeds the literal name of the procedure, something that's easy to get wrong as you copy/paste it into other procs.

Duration:	2 mins 26 secs
Skill Level:	100
Rating:	4.14 out of 5
Publish Date:	October 08, 2010

You must be logged in to view this video.

12345
1=Poor, 3=Good, 5=Excellent

About the Author

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

References

Demo Script

Comments

Todd Burrell on 10/8/2010

So is the story that in every stored procedure I develop, I could add the code to insert the runtime details into a logging table, and monitor how many times that stored procedure is called? If that is the case, then I definitely learned something new and plan on using it.

Jeff Benedict on 10/8/2010

Cool. Thanks.

Don Weigend on 10/8/2010

This is an excellent suggestion! We do logging like what was described and proc name is usually included in the log record. This tip will be a perfect fit for that.

borjonx on 10/8/2010

interesting

Paul on 10/8/2010

Yes, I’ve forgotten to change the logging proc name more times than I would like to admit to so yes, I immediately see the value. Simple but very useful. Thanks!

John O'Sullivan on 10/9/2010

very useful tip

Juri Kirillov on 10/10/2010

Thanks, another piece of knowledge has learned :)

Deepak on 10/10/2010

Excellent.

Logan Price on 10/10/2010

I saw someone do a really cool call stack using this technique. Here is the URL: http://www.thecodepage.com/post/TIP-A-Call-Stack-in-SQL-Server.aspx

Dana on 10/10/2010