Must declare the scalar variable "@@procid"

user_4802803 0
Viewed 918

I'm pretty new to SQL Server. I'm trying to create a simple procedure to get the name of the current procedure name for debugging purpose.

This is the code.

-- ======================================================================
-- Create Stored Procedure Template for Azure SQL Data Warehouse Database
-- ======================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [stg].[test_procid]
AS
BEGIN
    DECLARE @objectname VARCHAR(200);
    SET @objectname = QUOTENAME(object_schema_name(@@procid)) + '.' + QUOTENAME(object_name(@@procid));
END
GO

When I execute this code, I get this error.

Msg 137, Level 16, State 1, Line 8
Must declare the scalar variable "@@procid".

Not sure what I'm doing wrong. Please help

Update: Thanks @Larnu and @David. I'm using Azure SQL Data Warehouse and I think that is the reason. I want this information to be passed on to another procedure in a generic way than hardcoding the procedure name.

Is there any way to do it in Azure Data Warehouse?

azure-sqldw azure-sql-database sql-server
2 Answers

I think you can test the relevant query on beginning off your stored procedure : 

select object_name(objectid) from sys.dm_Exec_requests ec cross apply sys.dm_exec_sql_text(ec.sql_handle) where session_id=@@spid

@@spid : https://docs.microsoft.com/fr-fr/sql/t-sql/functions/spid-transact-sql?view=sql-server-2017

Update : 

select object_name(objectid) from sys.dm_Exec_requests AS EC cross apply sys.dm_exec_sql_text(EC.sql_handle) where session_id=@@spid
user_99408030

@@procid is not supported on Azure SQL Data Warehouse. This is documented.

It works fine on Azure SQL Database.

user_72977000
Related