cancel
Showing results for 
Search instead for 
Did you mean: 

How to call a stored procedure from Execute SQL and get the value

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)

Related Links


Labels: (2)
Version history
Revision #:
36 of 40
Last update:
2 weeks ago
Updated by: