How to inner join a result set from stored procedure without creating temp table

Question

How to inner join a result set from stored procedure without creating temp table

halifaxdal

SSCoach

Points: 19741
More actions
September 5, 2007 at 10:08 am

#118575

Hello,
I wonder how can I inner join the running result from my sp in my sql query:
e.g. select * from tableA A inner join (result of stored procedure) B on A.ID = B.ID
I might be able to create a temp table and dump the result of sp over there, do I have to use temp table? or there is an easy way to approach this?
Thanks.

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

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

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

You're going to want to dump it into a temporary table, table variable or if possible change the stored procedure to be a table-valued function (not always possible). Then you can join to it.

RonKyle SSC-Dedicated Points: 31537 More actions · Answer 2

Dumping it into a temp table isn't hard, but you could also consider using derived tables. The derived table is calculated within your parenthesis. I've included a link to a page for you to review. http://www.sqlteam.com/article/using-derived-tables-to-calculate-aggregate-values

halifaxdal SSCoach Points: 19741 More actions · Answer 3

Thank you guys for responding.

I am having more and more trouble:

To do an experiment, I created a temp table, and tried to inner join that table. Unfortunately that sp is on a linked server, it seems there is some restriction on getting data from a linked server, here is the error I encountered:

Query:

create table #t(observanceid int, observancetime datetime, observancetype int)

insert #t(observanceid, observancetime, observancetype) exec [MyServer].MyDB.dbo.listdata

Error1: MSDTC on server 'MyServer' is unavailable

Following a suggestion from http://geekswithblogs.net/narent/archive/2006/10/09/93544.aspx

I got error 2:

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

[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].

What the hell is going on? How can I resolve this issue?

All I need is:

I need to do some data analysis on a production server, so I don't have write permission to create sp directly on that server. Instead, what I am thinking is:

1. link the server to my shell db/server

2. write sp on my shell db/server

I have new tables created on my shell db/server, which I will need to join with data result returned from the sp on the linked server.

Or, considering the frequency of polling data, is there a way to create a kind of "back up" or view on shell db/server using that stored procedure on the linked server?

That's all my tasks.

Thank you very much.