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.

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  September 5, 2007 at 10:11 am

  #731566

  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: 31565

  More actions

  September 5, 2007 at 10:23 am

  #731573

  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

  September 5, 2007 at 10:57 am

  #731583

  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.