Syntax error Assign an SQL result to variable from prepared statement in MySQL?

Assign an SQL result to variable from prepared statement in MySQL?

For this, use stored procedure. Let us first create a table −

mysql> create table DemoTable(Id int, Name varchar(100));
Query OK, 0 rows affected (1.51 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values(10,'John');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values(11,'Chris');
Query OK, 1 row affected (0.41 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+------+-------+
| Id | Name    |
+------+-------+
| 10 | John    |
| 11 | Chris   |
+------+-------+
2 rows in set (0.00 sec)

Following is the query to assign SQL result to variable from prepared statement in MySQL −

mysql> DELIMITER //
mysql> CREATE PROCEDURE Prepared_Statement_Demo( nameOfTable VARCHAR(20), IN nameOfColumn VARCHAR(20), IN idColumnName INT)
BEGIN
   SET @holdResult=CONCAT('SELECT ', nameOfColumn, ' INTO @value FROM ', nameOfTable, ' WHERE id = ', idColumnName);
      PREPARE st FROM @holdResult;
      EXECUTE st;
      DEALLOCATE PREPARE st;
   END //
Query OK, 0 rows affected (0.20 sec)
mysql> DELIMITER ;

Call the stored procedure using call command −

mysql> call Prepared_Statement_Demo('DemoTable','Name',10);
Query OK, 0 rows affected, 2 warnings (0.00 sec)

Now check the value of variable @value −

mysql> select @value;

This will produce the following output −

+--------+
| @value |
+--------+
| John   |
+--------+
1 row in set (0.00 sec)
Updated on: 2019-08-22T07:42:59+05:30

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements