CODEWITHSUNDEEP
mysqlstored-procedures
Nikita
Nikita

Reputation: 33

Stored procedures using variable as table name and retrieve data in output variable

I am writing a stored procedure in which I am uaing variable as table name and also retrieve the result of the query in output parameter

Following is the stored procedure`

DELIMITER //
CREATE  PROCEDURE db_test.test(IN tblname varchar(100),OUT Valv varchar(100))
BEGIN
SET @a = CONCAT('SELECT name INTO  Valv FROM ', tbname ,' limit 1') ;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END;
//
DELIMITER ;

When I call the above procedure using below command

CALL db_test.test('db_test.t_names',@name);
select @name;

I am getting error as PROCEDURE db_test.test can't return a result set in the given context

I have searched solution for this error but could not find it. Please suggest solution for above query.

Upvotes: 1

Views: 2938

Answers (2)

fancyPants
fancyPants

Reputation: 51868

You have to use user variables in prepared statements. A bug report exists already.

DELIMITER //
CREATE  PROCEDURE db_test.test(IN tblname varchar(100),OUT Valv varchar(100))
BEGIN
SET @a = CONCAT('SELECT name INTO @Valv FROM ', tbname ,' limit 1') ;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SET Valv = @Valv;
END;
//
DELIMITER ;

Upvotes: 3

Kautil
Kautil

Reputation: 1331

try this

DELIMITER //
CREATE  PROCEDURE db_test.test(IN tblname varchar(100),OUT Valv varchar(100))
BEGIN
SET @a = CONCAT('SELECT name INTO  ', Valv ,' FROM ', tbname ,' limit 1') ;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END;
//
DELIMITER ;

Upvotes: 0

Related Questions