stored procedure with count(*) and if statement

Question

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

@gvee I've removed the sql-server tag as the error is MySQL related, but agree that looks like sql-server proc. benddror can you please clarify. — Tanner
– Tanner, Commented Feb 20, 2015 at 15:56
im working with phpmyadmin means MySql if thats what you ment by clarify. — benddror
– benddror, Commented Feb 20, 2015 at 16:07
so, somebody can please write me how it suppsed to be written in the syntax of MySql — benddror
– benddror, Commented Feb 20, 2015 at 16:19

Rubik · Accepted Answer · 2015-02-20 15:52:50Z

2

Try to invert those 2 lines :

...
declare @var int
AS
...

As follow :

...
AS
declare @var int
...

answered Feb 20, 2015 at 15:52

Rubik

1,4711 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

benddror Over a year ago

thank you for your answer, but it still give me the same error

Rubik Over a year ago

As mentionned by gvee and Tanner in comments of your question, it seems your trying to use MSSQL syntax within MySQL. With my answer your procedure compiles successfully in MSSQL. So there's probably some misunderstanding somewhere.

benddror Over a year ago

so, somebody can please write me how it suppsed to be written in the syntax of MySql

Federico Razzoli · Accepted Answer · 2015-02-20 17:54:11Z

0

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.

answered Feb 20, 2015 at 17:54

Federico Razzoli

5,5411 gold badge23 silver badges22 bronze badges

3 Comments

benddror Over a year ago

Thank you for your answer, i did evrey thing you said and lokked a little bit more in google but didnt find why its still not working, i updated my code to the new one, also i did it through phpmyadmin...

Federico Razzoli Over a year ago

1 Does phpmyadmin automatically enclode your code in BEGIN ... END? 2 Some ; are still missing 3 The subquery in the SET statement must be enclosed in paranthesis: SET var = (...);

Federico Razzoli Over a year ago

Also, there is no print function. Use SELECT 'Dah! already exists';

Collectives™ on Stack Overflow

stored procedure with count(*) and if statement

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related