Brandon E Hofmann <[EMAIL PROTECTED]> wrote on 12/21/2005
11:53:49 AM:
>
> Is it possible to have one stored procedure reference a result set
> generated by another stored procedure that it called?
>
> For example:
>
> sp_2 calls sp_1 to put a result set in a temporary table
>
> sp_2 then does a join with the same temporary table created by sp_1 for
a
> final result set
>
> I have a common stored procedure that determines access security and is
> used by subsequent stored procedures.
>
> Please let me know.
>
> Thanks,
>
> Brandon
>
That's about the only way you could do it as I am not sure that you can
treat the results of stored procedures as table data any other way. For
instance I am not at all sure you could write something like (rough code):
DELIMITER //
CREATE PROCEDURE test2()
BEGIN
SELECT *
FROM (call test1()) as s
WHERE s.column1 is not null;
END
//
DELIMITER ;
and get it to work (I don't have a 5.x server to play with or I would
try). The big thing you have to worry about is that you make sure you
reuse the same connection that you ran the first SPROC in or the temporary
table it creates will go away by the time you run the second SPROC.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
