Is that correct way how to declare variable in SQL?

Question

I am very new in SQL. I just want to create basic trigger. It seems there might the problem about variable declaration or assignment.

DECLARE @i INT = 0
DECLARE @j INT = 0
DECLARE @player_name VARCHAR(255), @player_team VARCHAR(255);
DECLARE @absence_list_length = SELECT COUNT(*) FROM nhl_absence;

WHILE @i < 30
BEGIN
    SET @player_name = SELECT playoff FROM `nhl_standings` where id like @i
    SET @player_team = SELECT skr FROM `nhl_standings` where id like @j

    WHILE @j < @absence_list_length
    BEGIN
        SELECT user FROM `nhl_absence` where name like @player_name;
        UPDATE nhl_absence SET team = @player_team;
    SET @j = @j + 1;
    END

    SET @player_team = SELECT skr FROM `nhl_standings` where id like @i
    UPDATE nhl_standings SET team = @player_team;

    SET @i = @i + 1;
END

Error message:

#1064 - Something is wrong in your syntax near 'DECLARE @i INT = 0 DECLARE @j INT = 0 DECLARE @player_name VARCHAR(255), @play' on line 1

Yes. Because I was trying to to create trigger via PhPMyAdmin. Params like trigger name, table name and so on, can be set by another way. — Boris Galický, Jul 23 '19 at 19:09
Possible duplicate of [MySQL: @variable vs. variable. What's the difference?](https://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference) — P.Salmon, Jul 24 '19 at 07:56

score 1 · Answer 1 · answered Jul 23 '19 at 19:14

1

If this is for MSSQL (Microsoft SQL Server), I think you need to declare and initialize the variable with 2 separate lines of code:

Instead of:

DECLARE @i INT = 0

Try:

DECLARE @i INT
SET @i = 0

See Variables (Transact-SQL) for details.

answered Jul 23 '19 at 19:14

jchampeau

11
2

Juan Carlos Oropeza · Answer 2 · 2019-07-23T19:34:40.457

local variables at contrary of user variables doesnt allow @. https://dev.mysql.com/doc/refman/8.0/en/declare-local-variable.html

Also you assign value using DEFAULT or SET

SQL DEMO

CREATE PROCEDURE simpleproc ()
BEGIN
    DECLARE i INT DEFAULT 5;
    DECLARE j INT DEFAULT 10;
    DECLARE player_name VARCHAR(255) DEFAULT 'JHON';  
    DECLARE player_team VARCHAR(255) DEFAULT 'BRONCOS';
    DECLARE absence_list_length INT;

    SET absence_list_length = (SELECT 5);

    SELECT i, j, player_name, player_team, absence_list_length;
END;

OUTPUT

Is that correct way how to declare variable in SQL?

2 Answers2