Results 1 to 8 of 8

Thread: Query result set of stored procedure

  1. #1
    Join Date
    Aug 2007
    Posts
    12

    Query result set of stored procedure

    I would like to make a selection of records returned by a stored procedure.

    E.g.

    SELECT Name FROM EXEC somestoredprocedure @age = 25

    This is wrong, but is there a way to do this?

    Maarten

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    What's somestoredprocedure? UDF?

  3. #3
    Join Date
    Aug 2007
    Posts
    12
    For instance a procedure returning name and address for persons with a certain age

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    You can't use stored procedure in select statement.

  5. #5
    Join Date
    Sep 2005
    Posts
    168
    SELECT tmp.*
    FROM OPENROWSET('SQLOLEDB', 'my_sqlserver_name';'my_sqluser_login';'my_sqlpass word',
    'EXEC mydatabase.dbo.mystoredprodedure') AS tmp

    --HTH--

  6. #6
    Join Date
    Aug 2007
    Posts
    12
    Thanks mikr0s, that works. Seems a bit strange though that you have to use OLEDB instead of calling the procedure directly.

  7. #7
    Join Date
    Sep 2005
    Posts
    168
    it can also be done without the OPENROWSET (but still OLEDB is used)

    --configure server for data access
    EXEC sp_serveroption @@servername, 'data access', true
    --get results of a sp like selecting from a table
    select * from openquery(my_servername, 'exec master.dbo.mytesting') a

    --where my_servername is the name of the sql server, returned by @@servername (not a configured linked server)

    --HTH--

  8. #8
    Join Date
    Oct 2007
    Posts
    3
    Typically the best approach is to place the output into a table/temp table/table valued variable:

    create proc testproc1
    as begin
    select 1
    union all
    select 2
    end
    go

    declare @procresults table (i int);
    insert @procresults exec testproc1;
    select * from @procresults;
    go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •