0

I am new bee at RDBMS, I want to get a row from one of the five tables and the name of table is value of one of the fields of another table. So I am trying to store the value in one variable from one query and then using that value query again in the table whose name is stored in a variable. I am writing queries in MySQL. I am getting errors in the syntax.

This are the query statements I am writing.

DECLARE @vcTemp VARCHAR(40)
SELECT unit_test_result.*, @vcTemp = unit_test.name
  FROM unit_test_result, unit_test
  WHERE unit_test_result.test_run_id = 3 && unit_test.id = unit_test_result.unit_test_id
SELECT @vcTemp.* FROM @vcTemp

But I am getting following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @vcTemp VARCHAR(40) SELECT unit_test_result.*, @vcTemp = unit_test.name ' at line 1

Can anyone help me with the syntax, please? Thanks.

Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
user2391188
  • 3
  • 3
  • you need to create a `function` or `stored procedure` to use dynamic query. – Kailash Yadav May 16 '13 at 18:22
  • I believe a similar question has been answered here: http://stackoverflow.com/questions/8809943/how-to-select-from-mysql-where-table-name-is-variable – Gaffe May 16 '13 at 18:29

1 Answers1

0

Dynamic table names aren't supported in SQL. If these queries are being run by a program in a some other language (i.e. a PHP script), then the easiest thing would be to do two queries -- one to get the table name, and another to get the actual data.

It is possible to do what you want using a stored procedure, though, if you're restricted to staying within MySQL. Here's an example: http://forums.mysql.com/read.php?98,126506,126598#msg-126598

Brian Rue
  • 126
  • 3