Stored Procedures in Select Statements

Stored Procedures in Select Statements

Post by Ron Malizi » Fri, 30 May 2003 17:43:32



I was wondering why is it that the following is an invalid
query:

Select * from EXEC sp_who

And is there a way to trick SQL Server into allowing it?
I guess my real question is: How can I use stored
procedures in a normal SQL query...much like nesting
stored procedures within stored procedures.

Thanks in advance
Ron Malizia

 
 
 

Stored Procedures in Select Statements

Post by Tom Morea » Fri, 30 May 2003 17:50:23


Ideally, you should convert the proc into a table-valued UDF.  However, if it's not your proc and you can't change it, try:

SELECT
   *
 FROM
   OPENROWSET
   (
     'SQLOLEDB'
   , 'SERVER=(local);Trusted_Connection=yes'
   , 'SET FMTONLY OFF EXEC sp_who'
   )

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional

www.pinnaclepublishing.com/sql

I was wondering why is it that the following is an invalid
query:

Select * from EXEC sp_who

And is there a way to trick SQL Server into allowing it?
I guess my real question is: How can I use stored
procedures in a normal SQL query...much like nesting
stored procedures within stored procedures.

Thanks in advance
Ron Malizia

 
 
 

Stored Procedures in Select Statements

Post by Edmund Sha » Fri, 30 May 2003 18:15:07


Superb, Tom. Always found that a bit tricky myself.

Edmund.

  Ideally, you should convert the proc into a table-valued UDF.  However, if it's not your proc and you can't change it, try:

  SELECT
     *
  FROM
     OPENROWSET
     (
       'SQLOLEDB'
     , 'SERVER=(local);Trusted_Connection=yes'
     , 'SET FMTONLY OFF EXEC sp_who'
     )

  --
  Tom

  ---------------------------------------------------------------
  Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
  SQL Server MVP
  Columnist, SQL Server Professional

  www.pinnaclepublishing.com/sql


  I was wondering why is it that the following is an invalid
  query:

  Select * from EXEC sp_who

  And is there a way to trick SQL Server into allowing it?
  I guess my real question is: How can I use stored
  procedures in a normal SQL query...much like nesting
  stored procedures within stored procedures.

  Thanks in advance
  Ron Malizia

 
 
 

Stored Procedures in Select Statements

Post by John E. Huschka [MSFT » Sat, 31 May 2003 01:34:22


Check out this page as well http://www.algonet.se/~sommar/share_data.html.
--
John E. Huschka
Microsoft Business Solutions/FRx

This posting is provided "AS IS" with no warranties, and confers no rights.
**********************************************

  Ideally, you should convert the proc into a table-valued UDF.  However, if it's not your proc and you can't change it, try:

  SELECT
     *
  FROM
     OPENROWSET
     (
       'SQLOLEDB'
     , 'SERVER=(local);Trusted_Connection=yes'
     , 'SET FMTONLY OFF EXEC sp_who'
     )

  --
  Tom

  ---------------------------------------------------------------
  Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
  SQL Server MVP
  Columnist, SQL Server Professional

  www.pinnaclepublishing.com/sql


  I was wondering why is it that the following is an invalid
  query:

  Select * from EXEC sp_who

  And is there a way to trick SQL Server into allowing it?
  I guess my real question is: How can I use stored
  procedures in a normal SQL query...much like nesting
  stored procedures within stored procedures.

  Thanks in advance
  Ron Malizia

 
 
 

Stored Procedures in Select Statements

Post by Edmund Sha » Sat, 31 May 2003 12:12:11


Very neat. Cheers.
The relative merits in the discussion are interesting (lthough all the other methods were known to me).

So, then, is that a Microsoft bug that INSERT-EXEC does not start an implicit transaction? or not?

??????

regards,
Edmund

  Check out this page as well http://www.algonet.se/~sommar/share_data.html.
  --
  John E. Huschka
  Microsoft Business Solutions/FRx

  This posting is provided "AS IS" with no warranties, and confers no rights.
  **********************************************

    Ideally, you should convert the proc into a table-valued UDF.  However, if it's not your proc and you can't change it, try:

    SELECT
       *
    FROM
       OPENROWSET
       (
         'SQLOLEDB'
       , 'SERVER=(local);Trusted_Connection=yes'
       , 'SET FMTONLY OFF EXEC sp_who'
       )

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional

    www.pinnaclepublishing.com/sql


    I was wondering why is it that the following is an invalid
    query:

    Select * from EXEC sp_who

    And is there a way to trick SQL Server into allowing it?
    I guess my real question is: How can I use stored
    procedures in a normal SQL query...much like nesting
    stored procedures within stored procedures.

    Thanks in advance
    Ron Malizia