Syntax error on DECLARE

Question

I want to use the serial of an insert directly in a next insert.

Based on Help with T-SQL script: Insert record, then use identity of that insert on another statement? I execute the following SQL query:

DECLARE @Id1 BIGINT
DECLARE @Id2 BIGINT
INSERT INTO doses (CPS, ground, total) VALUES (10, 10, 10)
SET @Id1 = SELECT SCOPE_IDENTITY()
INSERT INTO places (x, y, z, speed) VALUES (10, 10, 10, 10)
SET @Id2 = SELECT SCOPE_IDENTITY()
INSERT INTO measurements (time, place, note, dose, id_dataset) VALUES ('Test', @Id1, 'test', @Id2, 17)

This give me a syntax error and I can't find anything wrong with my DECLARE.

#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 @Id1 BIGINT
DECLARE @Id2 BIGINT INSERT INTO doses (CPS, ground, total)' at line 1

It seems that DECLARE in MySQL can only be used in a BEGIN..END. http://dev.mysql.com/doc/refman/5.0/en/declare.html

Also SCOPE_IDENTITY is replace with LAST_INSERT_ID().

This is my new query:

BEGIN

DECLARE @Id1 BIGINT;
DECLARE @Id2 BIGINT;

INSERT INTO doses (CPS, ground, total) VALUES (10, 10, 10);
SET @Id1 = (SELECT LAST_INSERT_ID());

INSERT INTO places (x, y, z, speed) VALUES (10, 10, 10, 10);
SET @Id2 = (SELECT LAST_INSERT_ID());

INSERT INTO measurements (time, place, note, dose, id_dataset);
VALUES ('Test', @Id1, 'test', @Id2, 17);

END

But I still get the same error.

No need to Select from the scope, its an output, Just remove the SELECT - `SET @Id1 = SCOPE_IDENTITY()` — Nadeem_MK, Sep 02 '14 at 10:13
Can you please post the full error message and the version of SQL Server you are using? — DrCopyPaste, Sep 02 '14 at 10:29
The error indicates you are using `MySQL` but the code you are trying to run against it is `SQL Server` Syntax; I think in MySQL `;` after each line is mandatory also there is no `SCOPE_IDENTITY()` in MySQL you should probably use `LAST_INSERT_ID()` instead. ([from this thread](http://stackoverflow.com/questions/560783/the-equivalent-of-sqlserver-function-scope-identity-in-mysql)) — DrCopyPaste, Sep 02 '14 at 10:40
I changed SCOPE_IDENTITY with LAST_INSERT_ID. Same problem. I also tried adding the ;. Same problem. I tried adding BIGINT(20) as data type. Same problem. — Milan, Sep 02 '14 at 10:52
did you add extra brackets to `LAST_INSERT_ID`? i.e. `SET @Id1 = (SELECT LAST_INSERT_ID());` — DrCopyPaste, Sep 02 '14 at 10:55

score 1 · Accepted Answer · edited May 23 '17 at 12:25

It seems you cannot declare and use variables outside triggers/procedures/functions or events. So you can also not work around this by just writing BEGIN...END around it.

Since you need two different ID values in one select-statement you could either wrap this into a stored procedure (that should circumvent the issue of not being able to declare variables) or just use the MAX-value of ID in both columns in your final insert-statement. (this of course assumes you have auto_increment enabled or at least ascending IDs in both tables)

So in essence you should be able to make it work if you replace your whole code with:

INSERT INTO doses (CPS, ground, total) VALUES (10, 10, 10);

INSERT INTO places (x, y, z, speed) VALUES (10, 10, 10, 10);

INSERT INTO measurements (time, place, note, dose, id_dataset)
VALUES ('Test', (SELECT MAX(ID) FROM doses), 'test', (SELECT MAX(ID) FROM places), 17);

You should also wrap this in an transaction to be safe that no other IDs have been inserted in the meantime.

Could you remove the ; after INSERT INTO measurements (time, place, note, dose, id_dataset); — Milan, Sep 02 '14 at 11:42

score 0 · Answer 2 · answered Sep 02 '14 at 11:15

0

its just a syntax error.

SELECT @ID1 = SCOPEIDENTITY() will do you I think

answered Sep 02 '14 at 11:15

Matthew Duizend

1
1

score -1 · Answer 3 · answered Sep 02 '14 at 09:59

-1

Seems issue with setting @Id1 and @Id2

    DECLARE @Id1 BIGINT
    DECLARE @Id2 BIGINT

    INSERT INTO doses (CPS, ground, total) VALUES (10, 10, 10)
    SET @Id1 = SCOPE_IDENTITY()

    INSERT INTO places (x, y, z, speed) VALUES (10, 10, 10, 10)
    SET @Id2 =SCOPE_IDENTITY()

    INSERT INTO measurements (time, place, note, dose, id_dataset) 
    VALUES ('Test', @Id1, 'test', @Id2, 17)

answered Sep 02 '14 at 09:59

huMpty duMpty

14,346
14
60
99

but wouldn't that mean that the question title is wrong? I see that you corrected an issue that should have been corrected but for that particular thing the error should have been `Incorrect syntax near the keyword 'SELECT'.` not `Syntax error on DECLARE` shouldn't it? – DrCopyPaste Sep 02 '14 at 10:02
@DrCopyPaste: I don't see any issue in variable declaration, I have point out the place it can go wrong!! – huMpty duMpty Sep 02 '14 at 10:04
This doesn't solve the problem. I also double checked my column names but they seem right. Also, then SQL won't give a syntax error on line 1. The same solution was pointed out in the comments of the answer of the linked thread. And it seems that both SELECT SCOPE_IDENTITY() and SCOPE_IDENTITY() are correct. – Milan Sep 02 '14 at 10:26
@Milan: See the [fiddle](http://sqlfiddle.com/#!3/d8579/1) do you have the primary key setup correct with the identity?? – huMpty duMpty Sep 02 '14 at 10:38
The primary keys of doses and places are both SERIAL, so BIGINT(20). The values in measurements are also BIGINT(20). How do set up this identity in phpMyAdmin? – Milan Sep 02 '14 at 10:43
http://dev.mysql.com/doc/refman/5.0/en/declare.html It seems DECLARE can only be used in a BEGIN...END statement in MySQL. – Milan Sep 02 '14 at 10:53
@Milan: Ohh, are you using `MySQL`?? I thought it's `sql-server`. Please update your question with correct tags, its misleading !!! – huMpty duMpty Sep 02 '14 at 10:58

Syntax error on DECLARE

3 Answers3