How to Share Data Between Stored Procedures using SSIS

Question

How to Share Data Between Stored Procedures using SSIS

ramsatish

Old Hand

Points: 380
More actions
February 23, 2009 at 8:45 am

#197442

Hi All,
I have a procedere which returns result set.
I want to use this result set in another stored procedure.
For ex
create proc test1
as
select orderid, customerid, employeeid from employees
create proc test2
as
create table temp2 (orderid int , customerid char(5), employeeid int)
insert into temp2 exec test3
How can we achieve this in SQL CLR Procedure
Thanks & Regards,
Sriram Satish

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

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

You are going to have to be a good bit more detailed in your requirements/expectations, but essentially, you don't get any special abilities by using SQLCLR when it comes to passing data. You still have the same limitations that normal TSQL procedures have, that being that you can't pass table variables (except in 2008) between procedures, local temp tables are locally scoped, and you have to either use a dynamically created table using a generated GUID and pass the GUID between procs so that they can reference the same table or pass the information as XML between procedures.

fuller.artful Right there with Babe Points: 795 More actions · Answer 2

The first question I have is, Why do it in CLR rather than T-SQL? If you are simply trying to learn CLR programming, that's one thing. But if you just want to get some work done, then I suggest that it's far simpler in T-SQL (2005/2008). Create a user-defined table function (i.e. a UDF that returns a table). Change your first sproc to a table UDF and then pass its result set to your second sproc. There are templates available on the Templates pane to help you do this.

Arthur Fuller
cell: 647-710-1314

Only two businesses refer to their clients as users: drug-dealing and software development.
-- Arthur Fuller

ramsatish Old Hand Points: 380 More actions · Answer 3

Hi,

Thanks for the reply.

I cant change the first stored procedure to a user defined function because I am using some dynamic sql in that procedure which cant be achieved in a function.So its mandatory to use stored procedrue there.

Using temporary table we can share the data between procedures.

But I think using temporary tables there would be impact on performance.

Using SSIS is there is any best mehod for achieving the same....?

Thanks & Regards,

Sriram Satish