How to retrieve object_id without hardcoding values for schema and stored procedure name

Question

In a stored procedure on Azure synapse Dedicated SQL pool I need to get the object_id of the currently executing stored procedure

SQL server has available extremely useful system variable @@PROCID

however @@PROCID is not available on Azure Synapse Analytics nor Analytics Platform System (PDW)

DECLARE @procid  VARCHAR(100) = ( SELECT OBJECT_ID([my-schema].[my-stored-proc-name]))

above works yet I am looking to avoid having to hardcode my schema and stored procedure name just to retrieve object_id

Essentially I just need a programmatic way to get [my-schema].[my-stored-proc-name] in my code inside a stored procedure as per

DECLARE @ProcName NVARCHAR(128) = '[my-schema].[my-stored-proc-name]'
DECLARE @procid   VARCHAR(100)  = (SELECT OBJECT_ID(@ProcName))

without having to hardcode both my schema and my stored procedure name

Answer 1

• You can use system dynamic management view sys.dm_pdw_exec_requests to retrieve the currently running stored procedure.

select substring(command,charindex('exec',command)+5,50) as current_SP FROM sys.dm_pdw_exec_requests where status='Running' and 
command like 'exec%' order by start_time

Stored Procedure:

create PROCEDURE usp_find_2
AS
BEGIN
select substring(command,charindex('exec',command)+5,50) as current_SP FROM sys.dm_pdw_exec_requests where status='Running' and 
command like 'exec%' order by start_time desc 
END

This stored procedure selects the command column from the sys.dm_pdw_exec_requests, which contains the command that is currently being executed. The substring function is used to extract the name of the stored procedure from the command. The charindex function is used to find the position of the string 'exec' in the command, and the substring function is used to extract the name of the stored procedure starting from the position of 'exec' plus 5 characters. The where clause filters the results to only show the currently running stored procedures.

• When this stored procedure is executed with the command,

exec usp_find_2

current stored procedure name is returned.