CODEWITHSUNDEEP
mysqlprocedure
benddror
benddror

Reputation: 11

stored procedure with count(*) and if statement

I have a problem with this sql code.

I have a table friends with three columns user1, user2, pending.

user1 and user2 are primary keys of datatype int.

The phpmyadmin returns this error:

MySQL said: #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 var int; Set var = SELECT COUNT(*) FROM friends WHERE ((user1 = id1 A' at line 1

note - i did it via phphmyadmin so i have This is my code: 

Declare var int; 
Set var = SELECT COUNT(*) FROM friends WHERE ((user1 = id1 AND user2=id2) OR (user1 = id2 AND user2=id1));
IF var = 0                                 
 BEGIN
    INSERT INTO friends
                ( user1, user2,pending)
         VALUES (id1, id2,1);
    Print 'Data now added.';
 END
ELSE
 BEGIN
     Print 'Dah! already exists';
 END

Upvotes: 0

Views: 1396

Answers (2)

Federico Razzoli
Federico Razzoli

Reputation: 5361

1) Everything (including declarations) must be in a BEGIN ... END block.

2) You don't need to (and cannot) declare @session_veriables, only local_variables.

3) Missing parameters. They must be declared between parantheses: proc_name(p1 INT, p2 INT)

4) All statements must terminate with ;

5) The whole procedure should be wrapped inside:

DELIMITER ||
...
||
DELIMITER ;

unless you send it via phpMyAdmin.

Upvotes: 0

Rubik
Rubik

Reputation: 1471

Try to invert those 2 lines :

...
declare @var int
AS
...

As follow :

...
AS
declare @var int
...

Upvotes: 2

Related Questions