SQL Server Stored Procedure Problem

nandakumar · Post by **nandakumar** » Sat Nov 10, 2007 9:48 am

Hi ,
Hi ,
I am trying to execute stored procedure using few input parameters getting from source table . And i need to get an output from the stored procedure. I need to pass input parameters dynamically. Is anyone can help me?

Thanks,
Nanda

chulett · Post by **chulett** » Sat Nov 10, 2007 9:53 am

Define 'dynamically'... based on what? What stage are you using? Have you tried using Job Parameters?

nandakumar · Post by **nandakumar** » Sat Nov 10, 2007 11:21 am

Thanks for your reply chulet

I am using ODBC stage to extract data from the table and need to pass that
values as inputs to my stored procedure in the next step.

If i got 100 rows from the table means i need to get 100 rows as output from the stored procedure

ray.wurlod · Post by **ray.wurlod** » Sat Nov 10, 2007 1:39 pm

From what stage type are you calling the stored procedure? ODBC? This would mean that you are calling the stored procedure once for each row extracted from the table, presumable expecting one row of "result" in each case? However you have not provided sufficient specific detail to permit more accurate analysis than that. What does the stored procedure do? Have you imported its definition?

nandakumar · Post by **nandakumar** » Sat Nov 10, 2007 3:49 pm

ray.wurlod wrote:From what stage type are you calling the stored procedure? ODBC? This would mean that you are calling the stored procedure once for each row extracted from the table, presumable expecting one row of ...

Ray,
I am extracting data from the table using ODBC stage ,then the next step is to call the stored proc using the out data from ODBC.

chulett · Post by **chulett** » Sat Nov 10, 2007 5:10 pm

Version 7.5.x includes a Stored Procedure stage, have you tried that? Or ODBC? More details, please.

nandakumar · Post by **nandakumar** » Sat Nov 10, 2007 5:44 pm

chulett wrote:Version 7.5.x includes a Stored Procedure stage, have you tried that? Or ODBC? More details, please. ...

Culett,
STP stage is not supporting the SQL Server stored procedure. We can use DRS stage to call stored proc and can load data into table. But in my case after extracting data from the table and (using ODBC stage here for extraction) next step need to give this data as input to my stored proc and get an output values from stored proc . Can we handle this problem in DataStage?

chulett · Post by **chulett** » Sat Nov 10, 2007 9:04 pm

Couldn't recall which 'S' it supported... Sybase. You'll need to use the ODBC stage:

1. Import the metadata from your SP in the Manager.
2. Set the Update Action in the ODBC stage to 'Call stored procedure'.
3. Set the 'Stored procedure name' by pointing to the imported metadata.

I personally don't have much truck with stored procedures in ETL. Another option would be for you to rebuild the functionality that it provides into the job itself so the procedure is no longer needed.

chulett · Post by **chulett** » Sat Nov 10, 2007 9:09 pm

Posting this I noticed that you also said 'and get output values' as well. That you will not be able to do. I believe that if your sproc does this, you will not be able to leverage it as a target, but then as I said I don't use stored procedures in my ETL.

If that's the case, you will then need to recreate the functionality in your job.

DSXchange

SQL Server Stored Procedure Problem

SQL Server Stored Procedure Problem

Re: SQL Server Stored Procedure Problem