Read multiple dataset (output) of a sql server procedure

atul9806 · Post by **atul9806** » Wed Mar 30, 2016 4:03 am

Hi Team

I never worked on SQL server

and current requirement is using a SQL server Procedure to fetch the data from DB and generate 3 output (with different metadata called dataset, this is same as if we run the 3 sql one after another). My requirement is to read all the output and do some manipulation.

Here, I am facing the problem, as Procedure will generate 3 outputs and I have to read all of them in one go, Can anyone help me how should I proceed?

Below are the solutions which I can think of -
1. Modify the SQL Procedure such a way that it will produce one output at a time
2. Modify the same to store the data into some temp table which datastage can use
Both solutions need to modify the procedure which I afraid is not allowed

I have very thin knowledge about SQL server so excuse me if I am asking very silly question.

Appreciate your comments and help.

chulett · Post by **chulett** » Wed Mar 30, 2016 6:56 am

I'm a little lost here. You have a stored procedure that somehow 'generates' three output datasets at once? Or is this 'one after the other'? And then exactly what do you need to do with this? Other than read in one go and do some manipulation, I mean. You have three different targets that you'll need to hit, one after the other, whenever their dataset arrives in the output?

Yikes.

In your shoes, my first question would be why do I need to use this stored procedure? What is it doing that I couldn't do in the ETL tool directly? Just because someone built it that doesn't necessarily mean I have to use the dang thing.

atul9806 · Post by **atul9806** » Thu Mar 31, 2016 1:12 am

I agree with you craig but Client (you know them very well

) is forcing to use that procedure to fetching the data. We have told them that can be coded in datastage also but he is not willing to use DS for fetching data. He want to RE-USE this procedure to fetch the data and apply other transformation in datastage.

Procedure is generated the output one after another with different metadata. AFAIK, we can not handle this kind of scenario in datastage. Please suggest If we can or manage procedure output somehow.

this will be something like this -

Calling Procedure --> 3 output (3 diff metadata) --here we have to read this data and manipulate it as per design.
As of now, we can think of to store these output to 3 target and then read from those tables and do the manipulations (which is again not a good design)

We are still trying to work with him to move totally on datastage. fingers crossed

ray.wurlod · Post by **ray.wurlod** » Thu Mar 31, 2016 4:37 pm

Are the three outputs delivered through three procedure arguments, or via its return value?

atul9806 · Post by **atul9806** » Mon Apr 04, 2016 6:14 am

Output data is delivered through its return value.

prasson_ibm · Post by **prasson_ibm** » Tue Apr 05, 2016 2:31 am

Hi,

You will have three options in stored procedure stage, source ,target and transform.

If you don't have any input to stored procedure, then use it as a source and you will have option of return code and error code on the output link I believe , and based on this you can do the transformation.

Please let me know if it helps you.