none
Using SELECT output from stored proc in another SELECT FROM?

    Question

  • Let stored proc be like this:

    Code Snippet

    CREATE PROCEDURE dbo.get_some_data

     ...
    AS

    SELECT * FROM anywhere ...

    GO

     

     

    Now, I want to use it like this:

    Code Snippet

    SELECT * FROM (EXEC get_some_data ...) WHERE some_condition

     

     

    meaning that the proc returns SELECT data.

     

    But, alas! "Incorrect syntax near the keyword 'exec'." !!!

    (SQL Server 2000)

     

    Is it possible what I need?

    Friday, August 01, 2008 12:33 PM

All replies

  • There are several ways to accomplish what you are trying to do.  A key question associated with this is whether or not the intended procedure updates anything.  Please comment on this.  Methods to accomplish what you want include:

    • Using INSERT INTO table EXECUTE procedure and then using the table for JOIN purposes -- typically a temp table or a table varable (if using SQL 2005) is used for this
    • You might want to consider converting your procedure to a function -- especially an inline function -- if possible.  (This is not possible if the procedure updates anything)
    • You might be able to use the OPENROWSET function to encapsulate the procedure.  (This will work even if the procedure does any updates; however, under these circumstances this idea becomes nasty)

     

    Friday, August 01, 2008 12:39 PM
    Moderator
  •  Kent Waldrop Au08 wrote:

    • Using INSERT INTO table EXECUTE procedure and then using the table for JOIN purposes -- typically a temp table or a table varable (if using SQL 2005) is used for this
    • You might want to consider converting your procedure to a function -- especially an inline function -- if possible.  (This is not possible if the procedure updates anything)
    • You might be able to use the OPENROWSET function to encapsulate the procedure.  (This will work even if the procedure does any updates; however, under these circumstances this idea becomes nasty)
      1. I use this method now, but it do not allow me for use it as subqueries (in other SELECT), i.e. to build temp table "dinamically" inside another SELECT query.
      2. I can not use function coz functions can't call stored procs (while I need to call them inside my proc).
      3. I will consider OPENROWSET yet.

      Friday, August 01, 2008 1:23 PM
    • you can wrap the original select into a function instead of a proc.

      Friday, August 01, 2008 2:59 PM
    • Try exec into a GLOBAL temp table.  You should be able to then refer to the table in other procs or queries.

       

      --This is your table definition

      create table ##test

      (

      test_id int,

      test varchar(6)

      )

      insert into ##test

      (

      test_id,

      test

      ) exec sp_myProc

       

      --You can run this from anywhere on the server in another session.

      select * from ##test

       

      drop table ##test

      Friday, August 01, 2008 5:53 PM
    •  SalJ wrote:
      Try exec into a GLOBAL temp table.  You should be able to then refer to the table in other procs or queries.

       

      --This is your table definition

      create table ##test

      (

      test_id int,

      test varchar(6)

      )

      insert into ##test

      (

      test_id,

      test

      ) exec sp_myProc

       

      --You can run this from anywhere on the server in another session.

      select * from ##test

       

      drop table ##test

      By using a global temp table you introduce a whole new set of problems, including dirty reads and concurrency, if multiple users execute code to create the global temp table.

       

      I would highly discourage the use of a global temp table.

      Friday, August 01, 2008 6:57 PM
      Moderator
    • True, if you run multiple times by different users, then you may have issues if you don't control properly.

       

      Friday, August 01, 2008 7:48 PM
    •  syi916 wrote:

      you can wrap the original select into a function instead of a proc.

      Unfortunately, I can't! Since UDFs can't call stored procs (what I need too). (SQL 2000)

      But, can SQL 2005 do it?
      Tuesday, August 05, 2008 3:07 PM
    • UDFs can call stored procs (by first calling an extended proc) :-)

       

      Here is a way to accomplish this but I myself would not run this on a production box

      http://sqlservercode.blogspot.com/2008/05/how-to-log-when-function-is-called.html

       

      Denis The SQL Menace

      SQL Server Programming Hacks

      ASP.NET hacks

       

       

       

      Tuesday, August 05, 2008 3:52 PM
      Moderator
    • Here is the OPENROWSET example

       

      Store The Output Of A Stored Procedure In A Table Without Creating A Table

       

      Keep in mind that this is a loop back query

       

      Denis The SQL Menace

      SQL Server Programming Hacks

      ASP.NET hacks

       

      Tuesday, August 05, 2008 3:56 PM
      Moderator