How to call a stored procedure from Execute SQL and get the value
When executing a stored Procedure in a Execute SQL Action the default value will be the execution status.
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)