SQL: Create table with term names (String containing years)

Question

I would like to create a table (two columns, first is auto incremental) which contains the term names, e.g. "SS 2000" and "WS 2000/2001" (for summer and winter term).

I tried the following:

CREATE PROCEDURE create_terms()
BEGIN
Declare @YearEnd integer;
SET @YearEnd = 2014;
Declare @YearFrom integer = @YearEnd - 100;
Declare @Term varchar = '';
while @YearFrom < @YearEnd Begin
    @Term = concat('SS ', @YearFrom);
  Insert into terms (term) VALUES @Term;
  Set @YearFrom = @YearFrom + 1;
End
END

but I already get an error in line 3: SQL query:

CREATE PROCEDURE create_terms() BEGIN Declare @YearEnd integer;

MySQL said: Documentation #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 '@YearEnd integer' at line 3

After reading the comments from Abishek and Stuart, I tried the following:

DELIMITER $$ 
DROP PROCEDURE IF EXISTS create_terms$$ 
CREATE PROCEDURE create_terms()
BEGIN
  DECLARE YearEnd INT;
  Declare YearFrom INT;
  Declare Term varchar(10);

  SET YearEnd = 2014;
  SET YearFrom = YearEnd - 100;
  SET Term= '';

WHILE (YearFrom < YearEnd) DO
  SET Term = concat('SS ', YearFrom);
  Insert into terms (term) VALUES (Term);
  Set YearFrom = YearFrom + 1;
 END WHILE;
END;

DELIMITER ;

This results in just the DROP PROCEDURE command being successfully processed. Even when removing this line and changing the first lines to:

 DELIMITER $$ 
 CREATE PROCEDURE create_terms()$$
 BEGIN

it doesn't work, the SQl console just writes "ERROR" and that's it.... :(

Just replace @YearEnd(all user variables) to YearEnd(to local variables) — avisheks, May 05 '14 at 08:26

score 1 · Answer 1 · edited May 23 '17 at 11:57

1

All variable staring @ are user variables and need not to be declared, procedures has their local variables not prefix with @, try like this:

DELIMITER $$
DROP PROCEDURE IF EXISTS create_terms$$
CREATE PROCEDURE create_terms()
BEGIN
Declare YearEnd integer;
SET YearEnd = 2014;
Declare YearFrom integer = YearEnd - 100;
Declare Term varchar = '';
while YearFrom < YearEnd Begin
    Term = concat('SS ', YearFrom);
  Insert into terms (term) VALUES YearFrom;
  Set YearFrom = YearFrom + 1;
End;
END;
DELIMITER ;

Here is help

edited May 23 '17 at 11:57

Community

1
1

answered May 05 '14 at 08:28

avisheks

1,178
10
27

Hi Avishek, thanks for the clarification about the variable declaration. Now I do get error: #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 '' at line 3 – AntonSack May 05 '14 at 08:35
@AntonSack, Try updated answer, you had delimiter issue – avisheks May 05 '14 at 08:41
Yes, that's it. Finally, the code is accepted BUT only the drop command is executed. Everything afterwards is just ignored... – AntonSack May 05 '14 at 08:50

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

1

There are quite a bunch of errors here

You should use procedure variables
The Syntax for insert is INSERT INTO Table(columns) VALUES(values);
You probably also want to insert the term, not the year end
Your syntax for while is wrong

CREATE PROCEDURE create_terms()
BEGIN
  DECLARE YearEnd INT;
  Declare YearFrom INT;
  Declare Term varchar(10);

  SET YearEnd = 2014;
  SET YearFrom = YearEnd - 100;
  SET Term= '';

  WHILE (YearFrom < YearEnd) DO
      SET Term = concat('SS ', YearFrom);
      Insert into terms (term) VALUES (Term);
      Set YearFrom = YearFrom + 1;
  END WHILE;
END

Fiddle here

edited May 23 '17 at 12:28

Community

1
1

answered May 05 '14 at 08:44

StuartLC

104,537
17
209
285

Stuart, I see that your code is working in SQLFiddle but when I copy it directly into my sql console, it throws the error: SQL query: CREATE PROCEDURE create_terms() BEGIN DECLARE YearEnd INT; MySQL said: Documentation #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 '' at line 3 – AntonSack May 05 '14 at 09:03
Check your use of delimiters - possibly you have used a delimiter of `;` - See avisheks answer for how to change delimiters. – StuartLC May 05 '14 at 09:04
Hi Stuart, I did so but this results in just the first command "DROP PROCEDURE" is being executed.... inline ` DELIMITER $$ DROP PROCEDURE IF EXISTS create_terms$$ CREATE PROCEDURE create_terms() BEGIN Declare YearEnd integer; SET YearEnd = 2014; Declare YearFrom integer = YearEnd - 100; Declare Term varchar = ''; while YearFrom < YearEnd Begin Term = concat('SS ', YearFrom); Insert into terms (term) VALUES YearFrom; Set YearFrom = YearFrom + 1; End; END; DELIMITER ;` – AntonSack May 05 '14 at 09:19
No, I meant just add `DELIMITER /` before you execute MY SqlFiddle answer on YOUR MySql server. On SqlFiddle, you change the Delimiter at the bottom drop down. – StuartLC May 05 '14 at 09:24

SQL: Create table with term names (String containing years)

2 Answers2