Joining results of a stored procedure to a table

Question

Post reply

Joining results of a stored procedure to a table

lallen-661487

Mr or Mrs. 500

Points: 531
More actions
March 6, 2008 at 11:01 am

#183467

I have a stored procedure that runs a dynamic sql query. The procedure works great and produces the results I expect.
I would like to join the results of this procedure to a table, but have been unable to do it. I think I may be able to do it with a temporary table, but some of the fields will be dynamically populated everytime the procedure is run. I won't know the field names ahead of time.
Any advice would be helpful.

Viewing 7 posts - 1 through 6 (of 6 total)

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

Here is an example. The output of the sp_spaceused returns two record sets.

CREATE TABLE #tmp(line VARCHAR(2000))

EXECUTE master.dbo.xp_cmdshell 'osql –SYourSQLMachine –E -Q"EXECUTE sp_spaceused" –o"c:\temp\sp_out.txt" –s"" '

INSERT INTO #tmp EXECUTE master.dbo.xp_cmdshell 'type "c:\temp\sp_out.txt" '

SELECT line FROM #tmp

DROP TABLE #tmp

Roberto.Perez SSC Veteran Points: 290 More actions · Answer 2

Imaging two tables "codes" and "reports" where codes is a common table having descriptions and reports has a column having a code and you want to display the description of each code.

Example:

SELECT a.descr, b.cde_action

FROM codes a

INNER JOIN ( SELECT DISTINCT cde_action FROM reports ) b

ON a.id_code = b.cde_action

My first result set returns one value only (multiples values does not work) and you can join that result set to another table.

Old version will be:

SELECT DISTINCT a.descr, b.cde_action

FROM codes a, reports b

WHERE a.id_code = b.cde_action

lallen-661487 Mr or Mrs. 500 Points: 531 More actions · Answer 3

The procedure produces a table, but the table fields can be dynamic, so that is the problem. It does not appear that a table can be created with dynamic field names. Even with the pivot table function, the field names have to be known ahead of time.

Roberto.Perez SSC Veteran Points: 290 More actions · Answer 4

event though your dynamic sql return different columns every time, you should include primary/foreigner keys in your result set or temp table otherwise how will you link that information to other tables?

lallen-661487 Mr or Mrs. 500 Points: 531 More actions · Answer 5

There are 2 primary key fields that will work to join to another table, but the remaining fields are dynamic.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Is there a chance to use the SP to generate the table?

For what it's worth - using SELECT...INTO instead of CREATE...INSERT INTO... would allow you to not have to specify the name.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?