Topic
When executing a stored Procedure in a Execute SQL Action the default value will be the execution status.
Instructions
If the intention is to get the value returned by the stored procedure than you must ensure the following:
1) The stored procedure as output parmater
2) You declare the output parameter in int eh query
Example:
Stored Procedure
USE [NorthWind]
GO
/****** Object: StoredProcedure [dbo].[getEmployeeId] Script Date: 5/29/2018 1:52:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getEmployeeId] @lastName nvarchar(30), @output int OUTPUT
AS
Select @output = EmployeeID
From Employees
Where LastName=@lastName
Execute SQL Query
Declare @output int
EXEC dbo.getEmployeeId @lastName='Davolio', @output = @output Out
Select @output
In the action Define a variable of the type being returned by the stored procedure( in the above example, the value being returned is an int) and then Specify the column name to retrieve as the output parameter (@output)
1) The stored procedure as output parmater
2) You declare the output parameter in int eh query
Example:
Stored Procedure
USE [NorthWind]
GO
/****** Object: StoredProcedure [dbo].[getEmployeeId] Script Date: 5/29/2018 1:52:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getEmployeeId] @lastName nvarchar(30), @output int OUTPUT
AS
Select @output = EmployeeID
From Employees
Where LastName=@lastName
Execute SQL Query
Declare @output int
EXEC dbo.getEmployeeId @lastName='Davolio', @output = @output Out
Select @output
In the action Define a variable of the type being returned by the stored procedure( in the above example, the value being returned is an int) and then Specify the column name to retrieve as the output parameter (@output)
Related Links
