passing table of data from one sp to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

passing table of data from one sp to another

Author

Topic

jtwork
Yak Posting Veteran

82 Posts

Posted - 2008-09-03 : 06:39:22

Hi can anyone point me in the direction of some good reference material for passing a table containing a single column of data (ref) from a strored proc to another stored proc for the second to use as a table in a select statement?

Never done this but i believe it can be done.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-03 : 06:48:45

You can use
1) Delimited string
2) XML

I prefer XML.

E 12°55'05.63"
N 56°04'39.26"

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-03 : 06:52:04

http://www.sommarskog.se/share_data.html

jtwork
Yak Posting Veteran

82 Posts

Posted - 2008-09-03 : 14:56:23

had a look over this but cant seem to get anything working.Is anyone able to provide a real simple example of passing a list of ref from a select query within an sp to a second sp so that the 2nd sp can use the ref's to join to another table and then import into a physical table.

sounds twisty but what im looking to do shouldnt be too complicated if i can get the code.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-03 : 16:24:30

[code]
Create @Tmp Table(myint int)

Insert Into @Tmp(MyInt)
Exec myStoredProcedure1

Declare @MyInt int
set @MyInt = 0

While exists(Select * from @Tmp a where a.MyInt < @MyInt)
BEGIN
Set @MyInt = (Select Min(MyInt) from @Tmp where MyInt > @MyInt )
Exec MyStoredProcedure2 @MyInt

End
[/code]

jtwork
Yak Posting Veteran

82 Posts

Posted - 2008-09-04 : 05:38:11

at which point would i insert into my physical table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 05:42:32

It seems like you can achieve what you want by making a table valued UDF which returns the table and then call this in sp to get the table. Can you give more info on your scenario so that i can give you a sample example.

jtwork
Yak Posting Veteran

82 Posts

Posted - 2008-09-04 : 05:52:32

Sure

Bit of history. I have a number of select statements i am making against a table (table1) i would then join the ref to 3 data tables on the ref and select many fields into a table. As this is going to be an insert the code would be very long as i have around 30 individual select statements. Rather than do this i thought it would be good if i could set up the insert into in a sp (sp2) and just pass the ref from each select query.

create sp1
Select query 1 Ref From table1
<From Here Push ref list Into sp2>

Select query 2 Ref From table1
<From Here Push ref list Into sp2>

Select query 3 Ref From table1
<From Here Push ref list Into sp2>

...

Go

Create Sp2
<On receiving list here tables will be joined to the list of ref (joining on ref) and will then insert the select statement into a physical table>
Go

This is a simple explanation as to how i want this to work. the select statement in sp2 will be large and called many times thorugh out sp1 which will help to save on me a really long sp1 query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 06:33:25

something like:-

CREATE FUNCTION Getdata
()
RETURNS TABLE
AS
BEGIN
RETURN
(
Select query 1 Ref,'query1' as cateegory From table1
<From Here Push ref list Into sp2>
union all
Select query 2 Ref,'query2' From table1
<From Here Push ref list Into sp2>
union all
Select query 3 Ref,'query3' From table1
<From Here Push ref list Into sp2>
)
END

Create Sp2
...
create table #UDF_Results
(
other fields as in udf,
category varchar(20)
)
INSERT INTO #UDF_Results
SELECT * FROM dbo.GetData()

then use this table for join. also use category field to get only part you want (if you want only partial results)

<On receiving list here tables will be joined to the list of ref (joining on ref) and will then insert the select statement into a physical table>
Go

jtwork
Yak Posting Veteran

82 Posts

Posted - 2008-09-04 : 07:10:34

this looks good i will have a play with this thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 07:19:55

quote:
Originally posted by jtwork

this looks good i will have a play with this thank you

You're welcome

Let me know how you got on with this.

jtwork
Yak Posting Veteran

82 Posts

Posted - 2008-09-04 : 11:25:48

im experiancing a problem

Server: Msg 170, Level 15, State 31, Procedure Getdata, Line 21
Line 21: Incorrect syntax near 'BEGIN'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 11:27:43

Care to show us the full code?

E 12°55'05.63"
N 56°04'39.26"

jtwork
Yak Posting Veteran

82 Posts

Posted - 2008-09-04 : 11:29:56

sorted it now. I took out begin and end from the function and it created fine. looks very useful thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 11:30:16

you dont require begin end there

CREATE FUNCTION Getdata
()
RETURNS TABLE
AS
RETURN
(
Select query 1 Ref,'query1' as cateegory From table1
<From Here Push ref list Into sp2>
union all
Select query 2 Ref,'query2' From table1
<From Here Push ref list Into sp2>
union all
Select query 3 Ref,'query3' From table1
<From Here Push ref list Into sp2>
)
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources