Current executing procedure name

Question

Is it possible to get the name of the current Stored Procedure in MS SQL Server?

Maybe there is a system variable or function like GETDATE()?

Alireza Maddah · Accepted Answer · 2016-08-14T04:49:07.490

169

You may try this:

SELECT OBJECT_NAME(@@PROCID)

Update: This command is still valid on SQL Server 2016.

edited Aug 14 '16 at 04:49

answered May 17 '11 at 17:11

Alireza Maddah

5,718
2
21
25

5

It's worth noting that the returned value is of type SYSNAME. – Buggieboy Apr 29 '15 at 14:18
what to do for function not procedure? any idea? Please help – Vinay Sinha Feb 26 '16 at 09:14
1

Still valid on SQL Serve 2012 – Pimenta May 06 '16 at 10:57
2

Still valid on SQL Server 2016 – Fka Jul 05 '16 at 16:48
2

Does not work for the session or global temporary stored procedures. – ajeh Feb 15 '17 at 16:13
@ajeh No, I get a random table name when I try this from a temporary stored procedure. – Elaskanator May 16 '18 at 15:38
1

@Elaskanator Provide your SQL like I did in the updated answer, we'll figure out what is going on. And your server version too, please. ATM I have access to SQL 2012 and 2016 and in both the result is NULL – ajeh May 16 '18 at 16:19
@Buggieboy Please elaborate: why is it worth noting the type returned is SYSNAME? – Bill Roberts Aug 10 '21 at 18:49
See my answer below if you need working code for session/global temp procedures :-) – Oreo Oct 27 '22 at 23:14

score 92 · Answer 2 · edited Jun 18 '13 at 06:55

92

OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)

edited Jun 18 '13 at 06:55

RAS

8,100
16
64
86

answered Jun 18 '13 at 06:39

karthik

921
6
2

3

If you use this inside a temp Proc, it returns NULL, with or without the schema name retrieval. 1st proc is "normal", 2nd is temp, in this code: `BEGIN TRAN GO CREATE PROC utility.TempProc AS SELECT OBJECT_SCHEMA_NAME(@@PROCID)+'.'+OBJECT_NAME(@@PROCID) GO EXEC utility.TempProc GO ROLLBACK GO BEGIN TRAN GO CREATE PROC utility.#TempProc AS SELECT OBJECT_SCHEMA_NAME(@@PROCID)+'.'+OBJECT_NAME(@@PROCID) GO EXEC utility.#TempProc GO ROLLBACK GO` – SAinCA Aug 04 '16 at 22:14
See my answer below for session/global temp procedures ^_^ – Oreo Oct 27 '22 at 23:16

score 16 · Answer 3 · answered May 17 '11 at 17:13

16

You can use OBJECT_NAME(@@PROCID)

Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger.

answered May 17 '11 at 17:13

Amit

21,570
27
74
94

score 10 · Answer 4 · edited Oct 31 '22 at 09:31

10

In the specific case where you are interested in the name of the currently executing temporary stored procedure, you can get it via:

select name
from tempdb.sys.procedures
where object_id = @@procid

You cannot use the accepted answer in SQL Server to find the name of the currently executing temporary stored procedure:

create procedure #p
as
select object_name(@@PROCID) as name
go
exec #p


name
------------------------------------
NULL

(1 row affected)

edited Oct 31 '22 at 09:31

Oreo

529
3
16

answered Feb 15 '17 at 16:19

ajeh

2,652
2
34
65

Can confirm, tested in Enterprise 2014 in 2008R2 compatibility mode with session-scoping (double ##) – Elaskanator May 16 '18 at 17:23
2

besides the fact, that it is right: Who would create temporary procedures?? :-D – Tarek Salha Jan 13 '20 at 12:14

Oreo · Answer 5 · 2020-08-12T17:15:02.797

You can check for NULL before getting the schema and name of the stored procedure.

This means that you can get the right data even for (global) temporary stored procedures (click image to make bigger):

USE [master]; --so we can test temp sprocs without cheating by being in tempdb.
GO

BEGIN TRAN;
GO

CREATE PROC dbo.NotTempProc
AS
BEGIN
    SELECT CASE
        WHEN OBJECT_SCHEMA_NAME(@@PROCID) IS NULL
        THEN OBJECT_SCHEMA_NAME(@@PROCID, 2) + N'.' + OBJECT_NAME(@@PROCID, 2)
        ELSE OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID)
        END AS ProcName;
END
GO

EXEC dbo.NotTempProc;
GO

CREATE PROC dbo.#TempProc
AS
BEGIN
    SELECT CASE
        WHEN OBJECT_SCHEMA_NAME(@@PROCID) IS NULL
        THEN OBJECT_SCHEMA_NAME(@@PROCID, 2) + N'.' + OBJECT_NAME(@@PROCID, 2)
        ELSE OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID)
        END AS ProcName;
END
GO

EXEC dbo.#TempProc;
GO

CREATE PROC dbo.##GlobalTempProc
AS
BEGIN
    SELECT CASE
        WHEN OBJECT_SCHEMA_NAME(@@PROCID) IS NULL
        THEN OBJECT_SCHEMA_NAME(@@PROCID, 2) + N'.' + OBJECT_NAME(@@PROCID, 2)
        ELSE OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID)
        END AS ProcName;
END


GO

EXEC dbo.##GlobalTempProc;
GO

ROLLBACK;

While you're likely to know whether the stored proc is a temp stored proc or not when you're writing the code for it I think this answer shows clearly the difference between the code you'd need to add to a temp stored proc vs a normal stored proc. — Simon Elms, Oct 26 '22 at 22:50

score 2 · Answer 6 · edited Jan 05 '23 at 14:45

I know this is old, but this is what I use. It appears to always work.

BEGIN TRAN
GO
-- Stored procedure, function of trigger
CREATE PROC dbo.TempProc AS
    DECLARE @DATETIME = GETDATE()
        ,@Me VARCHAR(64) = COALESCE (
             OBJECT_SCHEMA_NAME(@@PROCID, DB_ID())
            ,OBJECT_SCHEMA_NAME(@@PROCID, DB_ID('tempdb'))
            ,'session'
        )
        + '.'
        + COALESCE (
             OBJECT_NAME(@@PROCID, DB_ID())
            ,OBJECT_NAME(@@PROCID, DB_ID('tempdb'))
            ,'SQL'
        )

    SELECT ProcName = @Me
GO
EXEC dbo.TempProc
GO
ROLLBACK
GO
BEGIN TRAN
GO
-- Temp Stored procedure
CREATE PROC #TempProc AS
    DECLARE @DATETIME = GETDATE()
        ,@Me VARCHAR(64) = COALESCE (
             OBJECT_SCHEMA_NAME(@@PROCID, DB_ID())
            ,OBJECT_SCHEMA_NAME(@@PROCID, DB_ID('tempdb'))
            ,'session'
        )
        + '.'
        + COALESCE (
             OBJECT_NAME(@@PROCID, DB_ID())
            ,OBJECT_NAME(@@PROCID, DB_ID('tempdb'))
            ,'SQL'
        )

    SELECT ProcName = @Me
GO
EXEC #TempProc 
GO
ROLLBACK
GO
-- SSMS or direct SQL statement
DECLARE @DATETIME = GETDATE()
    ,@Me VARCHAR(64) = COALESCE (
         OBJECT_SCHEMA_NAME(@@PROCID, DB_ID())
        ,OBJECT_SCHEMA_NAME(@@PROCID, DB_ID('tempdb'))
        ,'session'
    )
    + '.'
    + COALESCE (
         OBJECT_NAME(@@PROCID, DB_ID())
        ,OBJECT_NAME(@@PROCID, DB_ID('tempdb'))
        ,'SQL'
    )
SELECT ProcName = @Me

Current executing procedure name

6 Answers6

Linked