How to pass parameter in MySQL query

MySQL allows you to pass parameters in your SQL query to dynamically query databases. You can do this using user variables, which are user-defined variables that can be used during MySQL session. In this article, we will learn how to pass parameter in MySQL query.

How to pass parameter in MySQL query

Here are the steps to pass parameter in MySQL query. Let us look at a simple example first. Log into MySQL prompt before you execute any of the following statement.

First, use SET command to define a variable and assign value to it. It has the following syntax.

SET @anyVariableName − = ’yourValue’;

We will set a variable id with a simple value.

mysql>set @id=1;

Now pass this variable in your MySQL query as shown in bold. Here is how you can use your user variable in SELECT and UPDATE statements.

mysql> select * from users where id=@id;

mysql> update users set name='John' where id=@id;

You can also use these user variables in prepared statements. Here is an example where have created a user variable @query that uses another user variable @tblname.

mysql> SET @tblName = 'Users';
mysql> SET @query = CONCAT('Select * FROM ', @tblName, ' LIMIT 10');
mysql> PREPARE stmt FROM @query;
mysql> EXECUTE stmt;
mysql> DEALLOCATE PREPARE stmt;

You can also save these statements in query.sql script.

SET @tblName = 'Users';
SET @query = CONCAT('Select * FROM ', @tblName, ' LIMIT 10');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Then you can always invoke your script as shown below, to run your queries.