Stored procedure into a table?

• Derek.Tapp

  SSC Rookie

  Points: 35

  More actions

  December 18, 2001 at 4:42 am

  #101054

  Hello,

  If you have a stored procedure like:

  create proc testProc

  as

  select * from myTable

  Is there a way of executing that via a select statement rather than just exec?

  e.g. The following doesn't work, but shows the idea of what I'm after:

  select * from (exec testProc) where id = 1

  i.e. Selecting from the result set of the stored procedure without altering the procedure itself and without using parameters

  Thanks for any help, can't find it anywhere.

• Paul Ibison

  SSCertifiable

  Points: 5303

  More actions

  December 18, 2001 at 4:47 am

  #421201

  UDFs (user defined functions) could be used instead of sps. Alternatively (and less preferably) OpenDataSource or OpenRowSet would work.

  Paul Ibison

  Paul.Ibison@btinternet.com

  
  Paul Ibison
  Paul.Ibison@replicationanswers.com

• nigelrivett

  SSCertifiable

  Points: 5472

  More actions

  December 18, 2001 at 8:52 am

  #421220

  Or you could just put the result into a temp table before the query.

  
  Cursors never.
  DTS - only when needed and never to control.
  

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738337

  More actions

  Follow

  December 18, 2001 at 9:57 am

  #421232

  I'd recommend UDFs in SQL 2000 and a temp table in earlier versions.

  Steve Jones

  steve@dkranch.net

• NigelFN

  Default port

  Points: 1467

  More actions

  August 22, 2002 at 5:04 am

  #435027

  One of the limitations of Functions is that they cannot store data in permanent tables.

  Is this the case also with OPENDATASOURCE and OPENROWSET?

  My pre-lim enquiries suggest that this you cannot do this.

  Many thanks for comments / suggestions