Stored procedures with output parameters

Published Mar 25 2019 01:55 PM 696 Views
Not applicable
First published on MSDN on Nov 08, 2006

When executing Stored Procedures with the Execute SQL Task, SSIS passes the statement to the underlying provider without parsing it. While efficient, the different ways of handling the input between the various providers can be a little frustrating. I was working on a couple of issues found in SP1, and realized that when dealing with stored procedures with output parameters, each provider wanted the SQL in a different format.


I'll stick with a simple stored procedure example that returns a single value.


CREATE PROC intoutput
@value INT OUTPUT
AS
SELECT @value = 10


I want my Execute SQL Task to run the stored procedure, and store the result in a package variable (varInt). For different providers, you'll need to change two main fields – the SQL Statement, and the Parameter Name value on the Parameter Mapping page.


OLE DB


SQL Statement: exec intoutput ? output
Parameter Name: 0


You should also set BypassPrepare to True, as the SQL can't be properly parsed if it contains a parameter marker.




1 – OLE DB General




2 – OLE DB Parameter Mapping


ODBC


SQL Statement: {call intoutput (?)}
Parameter Name: 1


The SQL statement for ODBC is very different than the others. You need to use "call" instead of "exec", and wrap the whole thing with curly braces. Also, the parameter numbering starts at 1 instead of 0.




3 - ODBC General




4 - ODBC Parameter Mapping


ADO


SQL Statement: intoutput
Parameter Name:<parameter name>


For ADO, you can use the stored procedure name by itself, and set the IsQueryStoredProcedure attribute to True. For parameter name, you use the name you gave the parameter in the stored procedure (in this case, "value").




5 - ADO General




6 - ADO Parameter Mapping


ADO.NET


SQL Statement: intoutput
Parameter Name:@<parameter name>


ADO.NET is similar to ADO. Set IsQueryStoredProcedure to True, and set your statement to the name of the SP you want to execute. Like ADO, you need to use the name of the parameter in the stored procedure for the Parameter Name column, prefixed with @ (in this case, "@value"). Note: The parameter mapping seems to work without the use of the @ sign, but I'm not sure if that works for all providers.




7 - ADO.NET General




8 - ADO.NET Parameter Mapping


Summary


Running stored procedures with the Execute SQL Task is tricky due to the differences in the way the providers handle the input SQL. The main differences are with the SQL Statement, and the Parameter Name value. The following table summarizes the differences (assumes a stored procedure of name StoredProc with a single output parameter named Param):


































Provider SQL Statement Parameter Name Notes
OLE DB exec StoredProc ? output 0 Set BypassPrepare to True
ODBC {call StoredProc (?)} 1
ADO StoredProc Param Set IsQueryStoredProcedure to True
ADO.NET StoredProc @Param Set IsQueryStoredProcedure to True

For more info about the Execute SQL Task, you can check out the books online entry , and the nice overview of the task on SQLIS.com.

Version history
Last update:
‎Mar 25 2019 01:55 PM
Updated by: