en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


Read first, before you you use the blog

Maximize
ינו12

Written by: ronen ariely
12/01/2019 05:37 RssIcon

Good day guys, 

Several days ago, i saw a question in the MSDN forums, which led me to write this blog. I will rephrase the original question a bit and discuss the following request: How can we get the name of the current Stored Procedure (SP) from inside the SP

This is actually very simple question with multiple solutions which we can use on SQL Server On-Premises, but these solutions are not supported in other frameworks like Azure Data Warehouse and Parallel Data Warehouse.

My goal is to provide solutions for Azure Data Warehouse and Parallel Data Warehouse, but on the way I will cover the common solution for SQL Server and Azure Database as well.

Option 1: Using @@PROCID and the function object_name

This is the official option and the recommended option if it is supported on the version of the database which you use.

DROP PROCEDURE IF EXISTS [RonenAri_GetProcedureName01_SP];
GO
CREATE PROCEDURE [RonenAri_GetProcedureName01_SP] AS BEGIN
    select
        ProcedureName = object_name(@@procid),
        SchemaName = object_schema_name(@@procid)
END
GO
EXECUTE [RonenAri_GetProcedureName01_SP]
GO

Documenthttps://docs.microsoft.com/en-us/sql/t-sql/functions/procid-transact-sql

Supported: SQL Server (starting with 2008); Azure SQL Database

Not Supported: Azure SQL Data Warehouse; Parallel Data Warehouse

  


Option 2: Using dm_exec_connections and dm_exec_sql_text

Using dm_exec_connections we can get the information about the connections established and using dm_exec_sql_text we can get the statement we used to create the current Stored Procedure (filtering by current session ID). 

DROP PROCEDURE IF EXISTS [RonenAri_GetProcedureName02_SP];
GO
CREATE PROCEDURE [RonenAri_GetProcedureName02_SP] AS BEGIN
    SELECT Txt.TEXT, C.most_recent_session_id
    FROM sys.dm_exec_connections C
    CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) AS Txt
    WHERE C.most_recent_session_id = @@spid
END
GO
EXECUTE [RonenAri_GetProcedureName02_SP]
GO

Documentsdm_exec_connectionsdm_exec_sql_text

Supported: SQL Server (starting with 2008); Azure SQL Database

Not Supported: Azure SQL Data Warehouse; Parallel Data Warehouse

  


Option 3: Using Error inside TRY/CATCH block

My trick will be to create fake Error and get the name of the procedure from the error's parameters.

ALTER PROCEDURE [RonenAri_GetProcedureName03_SP] AS BEGIN
    DECLARE @ProcName sysname
    -- Do your work here
 
    -- TRY/CATCH
    BEGIN TRY
        --DECLARE @T INT = 1/0;
        RAISERROR ('Ronen Ariely Fake Error',16,1);
    END TRY 
    BEGIN CATCH 
        SELECT @ProcName = ERROR_PROCEDURE()
    END CATCH
 
    -- You can usethe SP namefor any following query
    SELECT @ProcName as ProcedureName
END
GO
EXEC [RonenAri_GetProcedureName03_SP]



Documents: THIS BLOG :-)

Supported: SQL Server (starting with 2008); Azure SQL Database; Azure SQL Data Warehouse; Parallel Data Warehouse

Not Supported: none

 


Summarize

Let's put it all in one table

Option
Number 
Elements
in used
Is published
in other posts
(according to my search)
Is Supported 
SQL ServerAzure DatabaseAzure Data warehouseParallel Data Warehouse
Option 1@@PROCIDYesYesYesNoNo 
Option 2dm_exec_connections; dm_exec_sql_text
YesYesYesNoNo 
Option 3TRY/CATCH
No! This is My trickYesYesYes
Yes
 
Option 4       

I hope this trick will help you, and if you have more options then you can always contact me over Facebook or linkedin.

Regards,
Ronen Ariely