SEO

#1 (**permalink**) 02-28-2008, 05:38 AM

Hi all.

I'd like to be able to define a stored procedure that returns a
recordset ( I've done this bit ), and then select from this in another
query, eg:

CREATE DEFINER=`root`@`10.146.%` PROCEDURE `sp_MyClients`( IN ACCMAN INT )
BEGIN
select * from mirror_Clients where AccMan = ACCMAN;
END

.... and then:

select
C.FileNo, C.SubNo, L.AccountNo
from
( sp_MyClients ( 192 ) ) C inner join TLocations T on C.ClientID =
T.ClientID;

I've also tried adding 'call' immediately before the 'sp_MyClients' bit.
Either way, I get:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '( 192 ) ) C inner join TLocations T on C.ClientID =
T.ClientID' at line 1

I assume it's not possible to do this then?

I realise I can skip the stored procedure in this case and just add the
where clause from the SP to the query, but in some other cases, the SP
bit will be quite complex.

Dan

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

#2 (**permalink**) 02-28-2008, 05:39 AM

At 3:00 PM +1100 1/22/07, Daniel Kasak wrote:
>Hi all.
>
>I'd like to be able to define a stored procedure that returns a
>recordset ( I've done this bit ), and then select from this in
>another query, eg:
>
>CREATE DEFINER=`root`@`10.146.%` PROCEDURE `sp_MyClients`( IN ACCMAN INT )
>BEGIN
>select * from mirror_Clients where AccMan = ACCMAN;
>END
>
>... and then:
>
>select
>C.FileNo, C.SubNo, L.AccountNo
>from
>( sp_MyClients ( 192 ) ) C inner join TLocations T on C.ClientID = T.ClientID;
>
>I've also tried adding 'call' immediately before the 'sp_MyClients'
>bit. Either way, I get:
>
>ERROR 1064 (42000): You have an error in your SQL syntax; check the
>manual that corresponds to your MySQL server version for the right
>syntax to use near '( 192 ) ) C inner join TLocations T on
>C.ClientID = T.ClientID' at line 1
>
>I assume it's not possible to do this then?
>
>I realise I can skip the stored procedure in this case and just add
>the where clause from the SP to the query, but in some other cases,
>the SP bit will be quite complex.

Stored procedures that generate result sets return them directly to the
client, not to another query. Perhaps you could accomplish what you want
by selecting the result set into another table and reading from that table
in subsequent queries.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Include a stored procedure ( that returns a recordset ) in anotherquery