No ratings

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

 

Version history
Last update:
‎03-30-2022 01:51 AM
Updated by:
Contributors