T-SQL - Using a resultset from a Stored Procedure

[Steven J Jowett]

I have a stored procedure that creates Pivot/cross tab resultset. for example my data is as follows :

Id, Date, Value
1, 01/12/2009, 100
1, 02/12/2009, 110
2, 01/12/2009, 50
2, 02/12/2009, 55
2, 03/12/2009, 75
3, 02/12/2009, 100

The Stored Procedure would result a resultset as follows :-

Id, 01/12/2009, 02/12,2009, 03/12/2009
1, 100, 110, 0
2, 50, 55, 75
3, 0, 100, o

So far so good. Now I need to join the results of the stored procedure to a another table joined by Id, either in a View or another Stored Procedure. Can I do this, keeping in mind that the number of columns and the names of the colums are variable. The only constant is the Id column name. Thanks

Steve Jowett ------------------------- Real programmers don't comment their code. If it was hard to write, it should be hard to read.

[Mycroft Holmes]

You have a number of choice on how to transport a resultset from a proc Turn the proc into a view - always the first choice but rarely possible with a pivot Use a UDF - also dies on the requirement of dynamic SQL to define the columns Shove the resultset into an XML variable and pass it back as an out parameter - there are a number of articles on CP on the subject. I have used this once and while it worked I have a strong dislike for XML. Use a global temp table. This is my default option but it has some drawbacks like using the tempdb, house keeping the tables and race issues if it is a popular proc.

Never underestimate the power of human stupidity RAH

[Steven J Jowett]

Thanks for the reply. I think I can discount the first three options. I will investigate the other two options. Thanks again

Steve Jowett ------------------------- Real programmers don't comment their code. If it was hard to write, it should be hard to read.

[i j russell]

Try turning the procedure into a table-valued function.