Cant declare variable SQL

mysql sql variables declare
James Stevenson picture James Stevenson · Nov 10, 2011 · Viewed 13k times · Source

I've been trying to declare an integer variable but its just not working. Here is my query:

DECLARE @count INT
SET     @count = 5633

SELECT count(matchid) 
FROM   `matches`
WHERE   id = @count

Im getting this error:

Error Code: 1064. 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 @count INT
SET     @count = 5633

Please help :)

Answer

Gustav Bertram picture Gustav Bertram · Nov 10, 2011

According to the MySQL manual, DECLARE is only allowed inside a BEGIN ... END block, and must be at the start. You're also forgetting a semicolon on the end of each line. This should work for you:

SET @count = 5633;

SELECT count(*) 
FROM matches
WHERE id = @count;

COUNT(*) is faster in some cases.

Related questions

MySQL: @variable vs. variable. What's the difference?

In another question I posted someone told me that there is a difference between: @variable and: variable in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can someone elaborate on this for me?

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More