ms sql + join to stored procedure

Posted on 2011-05-05
Last Modified: 2012-05-11
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
Question by:dkilby
    LVL 51

    Expert Comment

    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.

    Author Comment

    how would i put the results of the stored procedure into a temp table?
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    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
    LVL 51

    Accepted Solution

    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
    LVL 51

    Expert Comment

    Oops. Sat on the page too long.  "What aneeshattingal said" ;-)
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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" ...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now