using SQL stored procedure results in stored procedure 
Author Message
 using SQL stored procedure results in stored procedure

OK, here's a trick question I would love an answer to.

I want to use the result table generated by a stored procedure
in the stored procedure which called it.

It is possible to retrieve the result table created by a stored procedure,
if you use some external library (DB-Lib, ODBC, etc.); is it
possible to do from with in a stored procedure?

The specific example I am trying is:

- Have multiple transaction log dumps to a single dump file
- The transaction logs in the dump file are serialized (1,2,3...)
- I can use LOAD HEADERONLY FROM <dumpfile> to retrieve a table
  listing the details (include the index numbers) in the output
  screen (or from an external client), but I can't reference the output
  from sql code.

There are other ways around this problem (which I have taken, which
are real *hacks*), but this is a common problem I run into: the info
I need I can get from a stored procedure, but I can't *use* the info
once I have it.

Any suggestions will be appricated.

Quote:
>> LKK <<



Sat, 08 Aug 1998 03:00:00 GMT
 using SQL stored procedure results in stored procedure

Quote:

> OK, here's a trick question I would love an answer to.

> I want to use the result table generated by a stored procedure
> in the stored procedure which called it.

> It is possible to retrieve the result table created by a stored procedure,
> if you use some external library (DB-Lib, ODBC, etc.); is it
> possible to do from with in a stored procedure?

> The specific example I am trying is:

> - Have multiple transaction log dumps to a single dump file
> - The transaction logs in the dump file are serialized (1,2,3...)
> - I can use LOAD HEADERONLY FROM <dumpfile> to retrieve a table
>   listing the details (include the index numbers) in the output
>   screen (or from an external client), but I can't reference the output
>   from sql code.

> There are other ways around this problem (which I have taken, which
> are real *hacks*), but this is a common problem I run into: the info
> I need I can get from a stored procedure, but I can't *use* the info
> once I have it.

> Any suggestions will be appricated.

> >> LKK <<

The only way I am aware of achieving this is by using the mail system you can then play around with the
results as strings or whatever you want. You have to put in some extra code if the query results exeed 255
bytes.

eg:
If you want to retain the results

create table (#)dumpheader
        (Dumptype char(8),
         DatabaseName char(30),
         Striped char(8),
         Compressed char(8),
         Sequence char(8),
         Volume char(8),
         Devicetype char(8),
         TableName char(30),
         Dumpsize char(8),
         Stripeset char(8),
         StripesetName char(30),
         Curseq char(16),
         Newseq char(8),
         CreationDate char(20),
         CreationTime char(16),
         ExpireDate char(16))


check status



   begin
        xp_findnextmsg (parameters) 'get id of next message
        xp_readmail    (parameters) 'set peek to true which leaves it as unread


                (any other parameters) ' with peek set to false

        ' some code to prevent infinite loop if message never arrives
    end                

You could do this sort of thing with any results and handle a whole range of different mail subjects which  
can be sent from stored procs or any mail user on the network.
The 'reciever' could run continually searching for the mail messages and the sending proc could wait for
the appropriate table entries to appear.

With some good defensive coding it could work quite well.

        Greg



Sun, 09 Aug 1998 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Using Stored Procedure's results in a Stored Procedure

2. Using the result from one stored procedure in a second stored procedure

3. Using Results Of Stored Procedure As Input Into Another Stored Procedure

4. Executing a Stored Procedure in a Stored Procedure and selecting on the result

5. Passing a result set from one stored procedure to another stored procedure

6. Returning a query result from a stored procedure into another stored procedure

7. Retrieving result from a stored procedure called within another stored procedure

8. Using Resultset in Stored Procedure in another stored procedure

9. Stored Procedure using another Stored Procedure

10. Using stored procedures within stored procedures

11. Stored procedures problem using DB2 Stored Procedure Builder

12. Calling a Java Stored Procedure from another Java Stored Stored Procedure


 
Powered by phpBB® Forum Software