Unknown system variable using DECLARE

Question

I have the following MySQL Query.

DECLARE RecCount Int Default 0;
SET RecCount=(select count(*) from tables where WaiterID=1 and date(TDate)=date(now()));

When I execute it, I get the following error:

Error Code: 1193. Unknown system variable 'RecCount'

So, I googled 'declaring variables in MySQL.' It appears that everyone uses DECLARE:

I don't understand why my query doesn't execute.

I'm using MYSQL WORKBENCH 6.0 CE and I eventually want to execute this query in PHP.

EDIT:
I have used the default as 0. If I remove the default 0, AND change the query to what I've written below, it executes. But it still doesn't save the result like it should!

SET RecCount=(select count(*) from tables where WaiterID=1 and date(TDate)=date(now()));
SELECT RecCount;

i have used the default as 0. and just without the default 0 — Jacques Koekemoer
– Jacques Koekemoer, Commented Oct 7, 2013 at 11:54
you want to post it as an answer then i can mark it as answered — Jacques Koekemoer
– Jacques Koekemoer, Commented Oct 7, 2013 at 11:57

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

You have to run that code in a procedure or function.

If you use control statements you have to use them in a function or procedure.

MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs.

Source

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 7, 2013 at 11:58

juergen d

205k40 gold badges303 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:08:25Z

The manual page for DECLARE you found says:

This statement declares local variables within stored programs.

If you use DECLARE somewhere else you get a syntax error:

mysql> DECLARE RecCount Int Default 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the r ight syntax to use near 'DECLARE RecCount Int Default 0' at line 1

Your first result clearly defines a BEGIN...END code block, which doesn't work (thus the question) because MySQL doesn't support anonymous code blocks.

It's possible that your MySQL client is not configured to abort on errors and you're only getting the last one.

I have the impression that you're after session variables but you don't use DECLARE for that: you use SET:

mysql> SET @foo='bar';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @foo;
+------+
| @foo |
+------+
| bar  |
+------+
1 row in set (0.00 sec)

Collectives™ on Stack Overflow

Unknown system variable using DECLARE

2 Answers 2

You have to run that code in a procedure or function.

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

You have to run that code in a procedure or function.

Comments

Comments

Linked

Related