mysql dynamic query in stored procedure

Question

i am creating a dynamic query in stored procedure. my stored procedure is as follows: ``` CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40),IN w_team VARCHAR(40)) BEGIN SET @t1 =CONCAT("SELECT * FROM ",tab_name," where team=",w_team); PREPARE stmt3 FROM @t1; EXECUTE stmt3; DEALLOCATE PREPARE stmt3; END ``` when i try to run it with the following call: ``` call test1 ('Test','SPA'); ``` i get the following error message: Error Code: 1054. Unknown column 'SPA' in 'where clause' i tested without where condition and it works fine, but with the where condition its not working, i tried using @ with the variable name but it still does not work. Thanks for your help.

Accepted Answer

You missed to enclose the parameter `w_team` in `WHERE` clause.

Try like this:

```
SET @t1 =CONCAT("SELECT * FROM ",tab_name," where team='",w_team,"'");
```

Explanation:

Query from your code would be like:

```
SELECT * FROM Test where team=SPA
```

It will try find a column `SPA` which is not available, hence the error.

And we changed it to:

```
SELECT * FROM Test where team='SPA'
```

mysql dynamic query in stored procedure

Solution

Problem