Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Question Unanswered: Dealing with a result set from a stored procedure

    Hi,
    I have a problem with dealing with result sets returned from stored procedures.

    I have a procedure like:
    CREATE PROCEDURE SampleProcedure
    AS
    BEGIN
    SELECT * FROM SampleTable
    END
    GO

    By executing this stored porocedure is returned result set containing data from SampleTable table. (EXECUTE SampleProcedure)

    The returned resultset can be seen in Query Analyzer and can be handled from ADO.NET without any hesitate.

    But I can't use this result set from other stored procedure. I tried:
    SELECT * FROM (EXEC SampleProcedure)
    But there is sintax error in select statement.

    Does anybody know, how to store the result set into a teporary table or select it by SELECT statement?

    Thanks.
    MarF.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    select * into #tmp from openquery(srvname,'exec sproc')
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    Yes it shoud work, but open query requires linked server name. I don't have to have any linked server registered bacause both stored procedures are in the same batabase.

    Originally posted by Enigma
    select * into #tmp from openquery(srvname,'exec sproc')

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Add a linked server to your own machine or use opendatasource
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Feb 2004
    Posts
    4
    Certainly,
    but I cant create Linked server. (because of security and administration issue).

    OPENDATASOURCE requires absolute definition of datasource. I need the database to be transferable (with diferent db name). So with absolute paths in stored procedures it wount be possible.

    I hope it exists very simple solution of my problem.

  6. #6
    Join Date
    Nov 2003
    Posts
    7

    Re: Dealing with a result set from a stored procedure

    If you don't need to return any other info from the sproc, you may wish to implement as a udf

Posting Permissions

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