how to display the out parameter value

[kaleeswaran]

hi!
i am new to myql procedure..
i can get the output value from the procdure if i use select option..but i could't get the value while i am using the out parameter.it shows null..
when i try to print the variable..
what is the pbm?........... ...
give me the solution...
thank you,
kalees

[amitpatel66]

hi!
i am new to myql procedure..
i can get the output value from the procdure if i use select option..but i could't get the value while i am using the out parameter.it shows null..
when i try to print the variable..
what is the pbm?........... ...
give me the solution...
thank you,
kalees

Kindly post the code that you have tried for my reference?

[kaleeswaran]

thank you for ur replay,
this is my code:
[code=mysql]
DELIMITER $$
DROP PROCEDURE IF EXISTS `bankpcb`.`samp le_pro` $$
CREATE DEFINER=`pinaka pcb`@`%` PROCEDURE `sample_pro`(id int,OUT type varchar(45))
BEGIN
select c_loan_type into type from t_lo_sanction where n_account_no=id ;
END $$
DELIMITER ;
[/code]
then i call the procedure like:
CALL sample_pro(2,@a );
then
i tried to select value @a:
select @a;
it shows null........

[amitpatel66]

thank you for ur replay,
this is my code:
DELIMITER $$
DROP PROCEDURE IF EXISTS `bankpcb`.`samp le_pro` $$
CREATE DEFINER=`pinaka pcb`@`%` PROCEDURE `sample_pro`(id int,OUT type varchar(45))
BEGIN
select c_loan_type into type from t_lo_sanction where n_account_no=id ;
END $$
DELIMITER ;

then i call the procedure like:
CALL sample_pro(2,@a );
then
i tried to select value @a:
select @a;
it shows null........

Is your table having data for account_id = 2?

[kaleeswaran]

yes i am having the value in this id...

[amitpatel66]

thank you for ur replay,
this is my code:
DELIMITER $$
DROP PROCEDURE IF EXISTS `bankpcb`.`samp le_pro` $$
CREATE DEFINER=`pinaka pcb`@`%` PROCEDURE `sample_pro`(id int,OUT type varchar(45))
BEGIN
select c_loan_type into type from t_lo_sanction where n_account_no=id ;
END $$
DELIMITER ;

then i call the procedure like:
CALL sample_pro(2,@a );
then
i tried to select value @a:
select @a;
it shows null........

You are using DEFINER=`pinaka pcb`@` in your procedure,
Are you calling a procedure from the same schema pinakapcb?
If you are calling a procedure from another schema, then the procedure will be called as definer's right and it will use the table t_lo_sanction available in the schema pinakapcb and not from the schema from where you are calling a procedure.

[amitpatel66]

yes i am having the value in this id...

Use PRINT command to print the value of the variable @a and let me know if it prints the value

Eg:

PRINT @a;