none
Must declare the scalar variable "@@procid"

    Question

  • I'm pretty new to mssql.

    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
    -- =============================================
    -- Author:      <Author, , Name>
    -- Create Date: <Create Date, , >
    -- Description: <Description, , >
    -- =============================================
    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

    Thursday, January 10, 2019 5:36 PM

All replies

  • Hi Anonymous2630,

    Are you using Azure SQL Database or Azure SQL Data Warehouse?

    I see references to both but @@PROCID (Transact-SQL) is only applicable to SQL Server and Azure SQL Database. In any case, you need to add a declare statement...I believe:

    CREATE PROCEDURE [stg].[test_procid]
    AS
    BEGIN
      declare @objectname varchar(200);
      declare @@procid int;
      set @objectname=quotename(object_schema_name(@@procid))+'.'+quotename(object_name(@@procid));

    I hope this helps!

    Thursday, January 10, 2019 11:03 PM
    Moderator
  • Actually I am not sure that is correct. I think you had it correct but are attempting to use this in Azure SQL Data Warehouse? Please confirm the database platform you are using. Thanks!
    Thursday, January 10, 2019 11:08 PM
    Moderator
  • Per your Stack Overflow thread with the same question: Must declare the scalar variable “@@procid”

    With Azure SQL Data Warehouse you can use @@spid 

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

     
    Thursday, January 10, 2019 11:40 PM
    Moderator
  • I'm using Azure SQL Data Warehouse
    Friday, January 11, 2019 3:05 PM
  • When I execute this sql it gives me the following error:

    Msg 103010, Level 16, State 1, Line 1
    Parse error at line: 25, column: 92: Incorrect syntax near 'ec'.


    Friday, January 11, 2019 3:07 PM
  • When I execute this sql it gives me the following error:

    Msg 103010, Level 16, State 1, Line 1
    Parse error at line: 25, column: 92: Incorrect syntax near 'ec'.


    Good day <?!?>

    * Off-topic: Really annoying that people do not even spend time to choose a nickname or usereal name in technical discussion community!

    >> When I execute this sql it gives me the following error:

    The view 'dm_exec_requests' is not supported in Azure Data Warehouse.

    * Please elaborate what is your actually needs and we will try to find the best option which fit Azure Data Warehouse.

     


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Friday, January 11, 2019 6:49 PM
    Moderator
  • I want this information to be passed on to another procedure in a generic way than hard coding the current procedure name.
    Friday, January 11, 2019 7:08 PM
  • I want this information to be passed on to another procedure in a generic way than hard coding the current procedure name.

    Hi,

    What is this which information you need to pass?
    Please try to think about what is your needs and not what you want to do. Maybe what you want to do is not supported. We need to understand "WHY YOU WANT TO DO IT" = "what is the needs"


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Friday, January 11, 2019 7:10 PM
    Moderator
  • I still waiting for the clarification, and I am not sure what is your needs, but in the meantime check if bellow script help you solve your needs

    Note! If you need to get the stored procedure ID in Data Warehouse,  then you can use sys.objects

    CREATE PROCEDURE p1 AS 
    	select 'sp1', @@SPID
    GO
    
    CREATE PROCEDURE p2 AS 
    	DECLARE @objectname varchar(200)
    	SELECT @objectname = quotename(object_schema_name(object_id ))+'.'+quotename(object_name(object_id ))
    	FROM sys.objects
    	WHERE type_desc = 'SQL_STORED_PROCEDURE' and name ='p1'
    
    	SELECT @objectname
    GO
    
    EXEC p2
    
    
    DROP PROCEDURE p2
    DROP PROCEDURE p1
    GO

    Another option for the second SP is bellow which use SYSNAME (much better! for most cases) and it execute nested Sp if this is what you need

    ALTER PROCEDURE p2 AS 
    	DECLARE @objectname SYSNAME
    	SELECT @objectname = quotename(object_schema_name(object_id ))+'.'+quotename(object_name(object_id ))
    	FROM sys.objects
    	WHERE type_desc = 'SQL_STORED_PROCEDURE' and name ='p1'
    
    	SELECT @objectname
    
    	EXEC p1
    GO
    
    EXEC p2

     


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Friday, January 11, 2019 7:41 PM
    Moderator
  • I wrote a very short blog on how to get the name of current stored procedure from the SP, using SQL Server, Azure Database, Azure Data Warehouse, and Parallel Data Warehouse

    http://ariely.info/Blog/tabid/83/EntryId/216/Get-the-name-of-current-stored-procedure-using-SQL-Server-Azure-Database-Azure-Data-Warehouse-and-Parallel-Data-Warehouse.aspx

    You can check the trick I am using there :-)

    * I will add more information during the day, but the code already there and you check if this solve your needs


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, January 12, 2019 6:56 AM
    Moderator