MySQL Stored Procedure syntax error with table variable

Question

I have this MySQL query to create a stored procedure:

Delimiter //;
Create Procedure addUser(
    IN facebookId varchar(20), 
        IN name varchar(50), 
        In accessToken varchar(100), 
        in expires float)
Begin
    Declare invitingUsers Table(Id varchar(20));

        Insert Into Users 
        (`Facebook_Id`,`Name`,`Access_Token`,`Expired`) Values (facebookId,name,accessToken,expires);

        Select Inviting_Id 
        From Invited_Users
        Where Invited_Id = facebookId
        Into invitingUsers;

        Update Table Users 
        Set Credit = Credit + 1 
        Where Facebook_Id In (Select Id From invitingUsers);
End//

but I'm keep getting this error - can't understand why:

#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 'Table(Id varchar(20)); Insert Into Users (`Facebook' at line 7

apparently mySql doesn't use table variable. the solution was to create a temp table — Asaf Nevo
– Asaf Nevo, Commented Jul 16, 2013 at 17:23

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

Change #1

Delimiter //; to Delimiter //

Change #2

Create a Temp Table in Memory

Change #3

Changed

Select Inviting_Id 
From Invited_Users
Where Invited_Id = facebookId
Into invitingUsers;

into

INSERT INTO invitingUsers          
Select Inviting_Id From Invited_Users
Where Invited_Id = facebookId;

With these changes I give you this:

Delimiter //
Create Procedure addUser(
    IN facebookId varchar(20), 
        IN name varchar(50), 
        In accessToken varchar(100), 
        in expires float)
Begin
        Declare invitingUsers Table(Id varchar(20));
        
        Create temporary table if not exists invitingUsers
        (Id varchar(20), PRIMARY KEY (id)) ENGINE=MEMORY;

        Insert Into Users 
        (`Facebook_Id`,`Name`,`Access_Token`,`Expired`)
        Values (facebookId,name,accessToken,expires);

        INSERT INTO invitingUsers          
        Select Inviting_Id From Invited_Users
        Where Invited_Id = facebookId;
        
        Update Table Users 
        Set Credit = Credit + 1 
        Where Facebook_Id In (Select Id From invitingUsers);
End//
Delimiter ;

Give it a Try !!!

Collectives™ on Stack Overflow

MySQL Stored Procedure syntax error with table variable

1 Answer 1

Change #1

Change #2

Change #3

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Change #1

Change #2

Change #3

Comments

Your Answer

Sign up or log in

Post as a guest

Related