Prepared Statements within stored procedures

[martinacevedo@gmail.com]

I have a stored proceduren with a prepared statement inside, I would
like to put the result fields into variables but I don´t know how,
Should I use some kind of cursor? How can I put the COUNT(*) field into
a variable at stored procedure scope?

CREATE PROCEDURE `GetPortability`(p_table VARCHAR(50), p_number
VARCHAR(50))
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SET @l_sql = CONCAT('SELECT COUNT(*) FROM ',p_table,' WHERE Prefix
LIKE \'',p_number , '\';');

PREPARE stmt1 FROM @l_sql;
EXECUTE stmt1 ;

DEALLOCATE PREPARE stmt1;
END;
Thank you....

[Giuseppe Maxia]

[email]martinacevedo@gmail.com[/email] wrote:

> I have a stored proceduren with a prepared statement inside, I would
> like to put the result fields into variables but I don´t know how,
> Should I use some kind of cursor? How can I put the COUNT(*) field into
> a variable at stored procedure scope?
>
> CREATE PROCEDURE `GetPortability`(p_table VARCHAR(50), p_number
> VARCHAR(50))
> NOT DETERMINISTIC
> SQL SECURITY DEFINER
> COMMENT ''
> BEGIN
> SET @l_sql = CONCAT('SELECT COUNT(*) FROM ',p_table,' WHERE Prefix
> LIKE \'',p_number , '\';');
>
> PREPARE stmt1 FROM @l_sql;
> EXECUTE stmt1 ;
>
> DEALLOCATE PREPARE stmt1;
> END;
> Thank you....
>

You can do it through a user variable.
For example:

delimiter //

drop procedure if exists GetPortability //

CREATE PROCEDURE `GetPortability`(p_table VARCHAR(50))
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare myvar int;
SET @l_sql = CONCAT('set @result = (SELECT COUNT(*) FROM ',p_table, ')' );

PREPARE stmt1 FROM @l_sql;
EXECUTE stmt1 ; -- here @result gets the value of COUNT(*)

DEALLOCATE PREPARE stmt1;
set myvar = @result;
-- do something with myvar
END//

delimiter ;


An alternative could be:

SET @l_sql = CONCAT('SELECT @result := COUNT(*) FROM ',p_table );

But it would also produce a dataset, which may not be what you want.

ciao
gmax

--
_ _ _ _
(_|| | |(_|>< The Data Charmer
_|
[url]http://datacharmer.org/[/url]

[martinacevedo@gmail.com]

Thank you Giusseppe, it works perfectly ...
Bye

[Unregistered]

Uh, what happened to the "WHERE Prefix..."?