Creating a procedure in mySql with parameters

Question

I am trying to make a stored procedure using mySQL. This procedure will validate a username and a password. I'm currently running mySQL 5.0.32 so it should be possible to create procedures.

Heres the code I've used. All I get is an SQL syntax error.

   GO
    CREATE PROCEDURE checkUser
    (IN @brugernavn varchar(64)),IN @password varchar(64))
    BEGIN
    SELECT COUNT(*)  FROM bruger WHERE bruger.brugernavn=@brugernavn AND bruger.pass=@Password;
    END;

Thank you in advance

Peter Rasmussen · Accepted Answer · 2014-04-06T19:50:42.200

80

I figured it out now. Here's the correct answer

CREATE PROCEDURE checkUser 
(
   brugernavn1 varchar(64),
   password varchar(64)
) 
BEGIN 
   SELECT COUNT(*) FROM bruger 
   WHERE bruger.brugernavn=brugernavn1 
   AND bruger.pass=password; 
END;

@ points to a global var in mysql. The above syntax is correct.

edited Apr 06 '14 at 19:50

answered Feb 18 '11 at 09:16

Peter Rasmussen

16,474
7
46
63

2

Deleted my previous post. Forgot that @ points to a global var in mysql (I usually work in Sql server where @ points to a normal parameter). If you want to check the syntax of a command, you can always go to the [mysql reference manual](http://dev.mysql.com/doc/refman/5.5/en/index.html). For CREATE PROCEDURE this would be [this page](http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html). – Sem Vanmeenen Feb 18 '11 at 09:28
See [this page](http://dev.mysql.com/doc/refman/5.1/en/commands-out-of-sync.html). – Sem Vanmeenen Feb 18 '11 at 09:37

score 8 · Answer 2 · edited Aug 02 '16 at 22:30

8

(IN @brugernavn varchar(64)**)**,IN @password varchar(64))

The problem is the )

edited Aug 02 '16 at 22:30

Stefan Falk

23,898
50
191
378

answered Apr 17 '13 at 17:39

Alexo

81
1
1

Brajesh · Answer 3 · 2018-09-22T17:15:27.500

Its very easy to create procedure in Mysql. Here, in my example I am going to create a procedure which is responsible to fetch all data from student table according to supplied name.

DELIMITER //
CREATE PROCEDURE getStudentInfo(IN s_name VARCHAR(64))
BEGIN
SELECT * FROM student_database.student s where s.sname = s_name;
END//
DELIMITER;

In the above example ,database and table names are student_database and student respectively. Note: Instead of s_name, you can also pass @s_name as global variable.

How to call procedure? Well! its very easy, simply you can call procedure by hitting this command

$mysql> CAll getStudentInfo('pass_required_name');

Creating a procedure in mySql with parameters

3 Answers3

Linked