October 28, 2011 at 8:57 am
Hi,
I got and Stored procedure whihc will be doing some Deletes, Inserts and Updates with in it into multiple tables.
Now it will get data from a table like
select COL1, COL2, COL3 from TableA
So now i got to pass them to the Stored procedures
Like
Exec SPTest1 COL1, COL2, COl3
SO i tried to use Two Execute SQL tasks ONe will select Data from the table and pass them to varibales and the next execute sql task get that variables as inputs. Here that varibales are wroking fine only for sinle row result set but i'm unable to figure it out how to use with Full rowset.
And i had another way also like having a dataflow task whihc copies data to a temp table and have an execute sql task whihc will execute the stored procedure but i'm not getting the way to pass the paramters to stored procedure with this.
Actually i can do that with OLE DB Command but it's taking lot of time whihc is causing us the performance issues. So i want to avaoid that OLE DB COMMAND
Can some one help me out with this please as it is very urgent.
October 28, 2011 at 10:45 am
I dont think that you can call the procedure in that way with a object, you really need to call the procedure with a single value for each parameter.
What you 'could' do is to loop through your object and pass each row to a variable and then call the proc with this varaible. Though I think this will still cause perfromance issues.
The way I would recommend doing an insert/update/delete operation in SS2K8 would be load you data into a staging table, rather than an object, and do your insert/update/deletes with a single MERGE statement.
this should perfrom a lot quicker than doing it row-by-row
October 28, 2011 at 12:10 pm
Can the second procedure call the first procedure and use the data from it that way. Then you just call the second procedure.
Otherwise, you'll need to take the recordset from the first procedure, store it in an Object variable in the package, and use a For Each Next loop to step through the rows in it and run the second procedure once per row.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2011 at 3:04 pm
Yes but I think doing a foreach loop on the resultset will probably be the same as the OLE DB command as far as performance goes.
GSquared's suggestion of calling the other stored procedure inside the first one is probably best.
If that's not possible for you then you're either stuck with the foreach loop or oledb command.
There is one other option, but it requires a good amount of .Net coding knowledge. You can have the first stored procedure store the result into an object variable/recordset destination and then use a script task to pass the object as a table valued input parameter to your second stored procedure.
October 28, 2011 at 4:26 pm
I still think a staging table and a Merge is your best bet for performance when it comes to upserts, but if you can let us know a bit more about what you are trying to acheive then we can give you a bit more specific advice.
March 13, 2014 at 7:30 am
Guys this is not the exact problem is ,The main Problem is we want to store result set from Stored procedure which is coming from a Select command inside Stored Proc .Now we need to capture it into Object type variable as Full result Set.Which is totally possible with Select Command .But getting error in case of Stored Procedure.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply