Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to inner join a result set from stored procedure without creating temp table Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2007 10:08 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 24, 2014 11:09 AM
Points: 831, Visits: 1,483
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.
Post #396555
Posted Wednesday, September 5, 2007 10:11 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:05 AM
Points: 779, Visits: 222
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.
Post #396558
Posted Wednesday, September 5, 2007 10:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:54 AM
Points: 820, Visits: 2,117
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


Post #396567
Posted Wednesday, September 5, 2007 10:57 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 24, 2014 11:09 AM
Points: 831, Visits: 1,483
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.



Post #396580
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse