Execute SQL Task output parameter vs ResultSet

Question

We have parameter direction as output in parameter binding and at the same time we do have result binding. So if we are having output variable or return type variable which will be available at output, so why do we need Result binding then.

Answer 1

ResultSets and output parameters are not the same, each one of them has it own use:

• ResultSets are used to store a result of a Select query: It can be a one or more columns and it can be a single row or a full result set. ResultSets are retrieved as ADO RecordSets and can be stored within variables. In general a RecordSet can be consumed on time.

• Output Parameters are used to store some values that can be set any part of the SQL command (not necessary at the end). Parameters have the same concept of a SQL stored procedure parameters. The value can be used several times.

You can have an Execute SQL Task with output parameters and a ResultSet.

---

Additional Information

• SQL Server Performance ResultSet vs Output Parameter vs Return Value
• Result Sets in the Execute SQL Task
• Map Result Sets to Variables in an Execute SQL Task
• Parameters and Return Codes in the Execute SQL Task

---

Update 1 @ 2019-16-08

You can use the output parameter as input parameter in another stored procedure but you have to execute it in a separate Execute SQL Task.

If you need to execute both stored procedures within one Execute SQL Task, then you can use SQL variables as mentioned in the example below:

DECLARE @output VARCHAR(50)

EXEC proc1 @output OUTPUT

EXEC proc2 @output

---

Update 2 @ 2019-19-09

Recently I published a detailed article about this topic on SQL Shack, you can check it on:

• Execute SQL Tasks in SSIS: Output Parameters Vs Result Sets