Output variable coming as Null after running Stored procedure

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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