Output variable coming as Null after running Stored procedure

salilsingh-9961 346 Reputation points
2023-04-09T18:03:46.17+00:00

Hi Team, While working on a Stored Procedure in SQL Server, by using print I am able to see the contents of an output variable but when I am running the stored procedure, output variable is shown as null. Why this might be happening as I am getting the output variable content while using Print(able to see the content in Message after running the stored procedure). Requesting to please help here. Thanks, Salil

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,894 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Viorel 112.9K Reputation points
    2023-04-09T18:33:19.5366667+00:00

    Use the OUTPUT keyword for the parameter and for the argument.


  2. Erland Sommarskog 102.3K Reputation points
    2023-04-09T18:43:57.52+00:00

    You really need to include an example of the code you are running, or else we are left to guessing. But as Viorel points out, you need to use OUTPUT both with the parameter declaration and the call.

    CREATE PROCEDURE dCREATE PROCEDURE demosp @a int, @b int OUTPUT AS
    SELECT @b = 2 * @a
    PRINT concat('Inside the procedure: ', @b)
    go
    DECLARE @double int
    EXEC demosp 19, @double OUTPUT
    PRINT concat('After the procedure: ', @double)
    go
    DROP PROCEDURE demosp
    
    0 comments No comments

  3. CosmogHong-MSFT 23,716 Reputation points Microsoft Vendor
    2023-04-10T02:12:47.2166667+00:00

    Hi @salilsingh-9961 You help yourself a lot if you post the SP code. Now, as Erland answered, what we can do is just guess.

    If you want to output a variable content after execute SP, you need to create another variable to receive this output value.

    Please check this sample:

    Create Table employees(employee_id int,salary money )
    Insert Into employees Values(200,8000)
    
    CREATE PROC emp_salary
      @employee_id INT,   
      @salary money OUTPUT   
    AS 
    SELECT @salary=salary FROM employees 
    WHERE employee_id=@employee_id
    
    DECLARE @salary_out money --first   
    EXEC emp_salary @employee_id=200,@salary=@salary_out OUTPUT 
    
    SELECT @salary_out
    

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments