Input to a stored Procedure

[mab_arif16]

Hi
I it possible to give an sequnetial file as input to a stored procedure.I want to pass file data as input to the procedure parameters .
If it is possible then what will be the syntax in stored procedure
Thanks
Arif

[ray.wurlod]

Your job will parse each line from the sequential file into a row (a set of columns). These can then be transformed/enriched within the DataStage job, which continues to pass rows through the job design. When you invoke your stored procedure, you do so once for each row processed, supplying column values from the input row to the stage from which you invoke the SP to replace the stored procedure's arguments.

[mab_arif16]

Hi Ray
I have just bought the membership for charter members ,but still I am not able to view all the contents ,do you have any idea how much time it will take to activate the membership.
Thanks
Arif

[chulett]

Are you using the actual Stored Procedure stage or some other mechanism?

[ray.wurlod]

Hi Ray
I have just bought the membership for charter members ,but still I am not able to view all the contents ,do you have any idea how much time it will take to activate the membership.
Thanks
Arif

I thought it was immediate. Contact either the Editor or the Webmaster (choose Contact from the menu at top). You may, however, need to re-connect to pick up the new setting.

[mab_arif16]

Hi Ray
If the input columns are col1,col2 ...etc. from input files , and the parameters to stored procedure are @ input1,@input2..etc
In the syntax to call the procedure
Will the syntax of stored procedure be #procedurename#@input1=col1,@input2=col2 or
#procedurename#@input1=DSLink.col1,@input2=DSLink.col2
OR in the parameters tab of SP stage we have to map the output column name to the parameters name .

[mab_arif16]

I am using the actual stored procedure stage .I dont think it is possible to give an input to the ODBC stage which calls stored procedure.

[DSguru2B]

Will the syntax of stored procedure be #procedurename#@input1=col1,@input2=col2 or
#procedurename#@input1=DSLink.col1,@input2=DSLink.col2
OR in the parameters tab of SP stage we have to map the output column name to the parameters name .

In the SP stage you will have to map the column. The input columns like as mentioned will be parsed to the input columns of your SP.
The tabs in the stage explicitly allow you to enter them.

[mab_arif16]

Hi guru
I have tried to give the input column name in maps to column in parameter tab,but I am getting error ,is there some changes needed to be done to done to procedure call syntax in the SP stage as well.

[DSguru2B]

In the stored procedure stage under the
Stage tab - > Parameters:
Enter the all the parameters. Meaning that all your inputs to the Stored Procedure and output to the stored procedure. Give the type of each one accordingly ie; input,output,function.. .

Stage tab -> Syntax
Give the procedure name
for eg: #pDbSchema#.dstage_batch_activate.activateBatch
and check mark Generate Procedure call.
You will see the generated syntax of the call statment made by the stage.

Then under the Input Tab -> General:
Check mark Execute procdure for each row and set transaction size as required.
Then goto the Input tab -> Column
Your input column should appear there.

Do the similar settings for the Output tab.
If your procedure is returning multiple rows then you need to give the End of Data code that your procedure will return.