• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 715
  • Last Modified:

ms sql + join to stored procedure

Is there anyway to join a table to a stored procedure?

something like

select t1.column1, t2.column2
from table1 t1
join storedprocedure t2 on t2.column1 = t1.column1
0
dkilby
Asked:
dkilby
1 Solution
 
_agx_Commented:
Nope.  AFAIK the best you can do is insert the procedure's results into a temp table/table variable. Then use that in your join.  Either that or convert the proc to something that can be used in a join, like a view or table valued udf.
0
 
dkilbyAuthor Commented:
how would i put the results of the stored procedure into a temp table?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you need to create a temp table exactly the same structure the sp returns, for example if the sp return 2 columns then you need to create a table with 2 columns.
in order to insert the data just run youe sp like this

insert into #tempTable
EXEC spName
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
_agx_Commented:
create a temp table that mirrors the columns returned by the proc

    create table #resultsOfMyStoredProc
    (
    column1 int,
    column2 int
    )

.. then execute the proc with insert into

      insert into #resultsOfMyStoredProc
      exec mystoredprocedure
0
 
_agx_Commented:
Oops. Sat on the page too long.  "What aneeshattingal said" ;-)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you CAN modify the procedure to be a function, you could save the step to create the temp table.
not possibly with all procedures, but normally worth the change.
if you need to "also" keep the procedure, you could still make the procedure use the function to avoid the "code duplication" ...
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now