JOIN

Question

JOIN

Mike Levan

SSChampion

Points: 14201
More actions
June 26, 2008 at 9:59 am

#72185

How can i join a table with the result set of a store proc.
or do i need to creayte a temp table insert the result set and then join.
something like this
CREATE TABLE #temp
(1,2,3...15 columns)
INSERT INTO #temp
SELECT 1,2,3,4,5,6,7,8,9 from table1
inner join table 2
inner join table 3
inner join (EXEC StoreProc 1...returns columns 10,11,12,13,14,15)
where clause...
Group by clause...
Having clause...
Order by clause...
SELECT * FROM #temp
DROP TABLE #temp
END
I am very much concerned about performance also.
thanks

Viewing 15 posts - 1 through 15 (of 17 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

You need to populate a temp table with the results from the stored proc and then join to the temp table. Unfortunately SQL Server does not let you treat a stored procedure like a table (InterBase does, not sure what other RDBS do however).

😎

Steve Jones - SSC Editor SSC Guru Points: 736627 More actions · Answer 2

What are you trying to do? You can create a temp table and assign the output of the proc to it.

Can you not perform the join in the proc?

Mike Levan SSChampion Points: 14201 More actions · Answer 3

Steve Jones - Editor (6/26/2008)
What are you trying to do? You can create a temp table and assign the output of the proc to it.
Can you not perform the join in the proc?

Do you mean join to a proc can be performed?

how?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

No. He asking if you can add the table you want to join into the stored proc and join it to the other tables there.

😎

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

Mike Levan (6/26/2008)
Steve Jones - Editor (6/26/2008)
What are you trying to do? You can create a temp table and assign the output of the proc to it.
Can you not perform the join in the proc?
Do you mean join to a proc can be performed?
how?

No - he's suggesting to have the stored procedure do it all (what it's doing now AND perform the join), so that the proc output is what you want.

If you can't "touch" the proc, Lynn's already covered the essence of it. the output from a SP isn't directly usable that way - you need to dump it to a table a join to it.

----------------------------------------------------------------------------------
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?

Mike Levan SSChampion Points: 14201 More actions · Answer 6

I get this error

[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Msg 7391, Level 16, State 1, Procedure Report11, Line 24

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Take a read of BOL. I think I have seen this before, and you may need to use OPENROWSET.

😎

Mike Levan SSChampion Points: 14201 More actions · Answer 8

thanks for the reply.

How can i use OPENROWSET in this scenario, i never worked with it.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Looking at BOL, it looks like you use just like a table.

I would still load the data into a temp table before doing the join.

😎

Mike Levan SSChampion Points: 14201 More actions · Answer 10

I am already using a Linked Server why do i need to go for OPENROWSET which opens a different connection again.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Not that familiar with the behind the scenes processing. I just seem to remember seeing something similiar to this before and this was the suggestion given. Can't hurt to try.

If you still have the same problem, then you need to go outside of SQL to check/correct MSDTC. Exactly what you need to do there is dependent on the version of Windows Server is running on both servers.

A search of SSC on MSDTC should hopefully identify the threads where this is aslo discussed, as I know it has also come up before.

😎