How to pass input and output parameters in Execute SQL Task?

Question

Post reply

How to pass input and output parameters in Execute SQL Task?

guptaopus

SSCommitted

Points: 1534
More actions
March 12, 2008 at 1:24 am

#120459

Hi,
I have a SP which requires 3 inputs and retuns 3 outputs I want to store that output values to variables. I have declared 6 variables (3 for i/p & 3 for o/p) I am not able to map correctly please any body help me.
my current settings are
Connection: OLE DB
SP: EXEC ODS_VLD_Header ?, ?, ?, ? output, ? output, ? output
Resultset: None
mapping: 0,1,2-i/p 0,1,2-o/p
Please find above snapshots for more details

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

XPSCodes-667952 SSChasing Mays Points: 603 More actions · Answer 1

In parameter name, I think you have to mention the parameters from SP.

For e.g

if your SP is something like this

ODS_VLD_Header (@Parm1 Int,@parm2 Int.. etc)

You would need to mention @Parm1,@Parm2 under parameter name in parameter mapping. Also, you can get rid of the ?,? in the sql statement (shown in general.bmp)

--------------------------------------------------------------------------------------
Save our world, its all we have! A must watch video Pale Blue Dot[/url]

Yihong SSChasing Mays Points: 624 More actions · Answer 2

I have the same problem, need to pass a input parameter to a stored procedure. I have the following in General setting:

ConnectionType = OLE DB

Connection = ServerName.DBName

SQLSourceType = Direct input

SQLStatement = Exec dbo.spName ?

My parameter needs to get date from

"select Max(date) from TableName"

I got rid of the "?", but the Build Query says procedure expects parameter, which was not supplied.

What did I miss? How to map the parameter? Thanks.

XPSCodes-667952 SSChasing Mays Points: 603 More actions · Answer 3

Attached a sample with ADO.net and OLEDB samples. Hope this helps.

You can check this link for more detailed information

http://technet.microsoft.com/en-us/library/ms141003.aspx

--------------------------------------------------------------------------------------
Save our world, its all we have! A must watch video Pale Blue Dot[/url]

Yihong SSChasing Mays Points: 624 More actions · Answer 4

Thanks for the example.

I coded the max(date) in script task and saved it as a variable, then passed this variable to sp in Execute SQL Task.