[SQL] Stored Procedure

What its suppose to do is. You submit Login + Pass + '0', and If the Login + Pass matches the info in the selected Table(accounts) it changes the '0' to '1' and returns it as a int value. If incorrect, it returns '0' unchanged. I get a error while using it in Navicat. Can anyone help?

Code:

---

CREATE PROCEDURE `CheckLogin`(Login varchar(25),PASS varchar(10),OUT ReturnValue int)
BEGIN
Declare ReturnValue int;
set ReturnValue =0;
set ReturnValue = (select count(*) as ReturnVal from accounts where Login like ID and Pwd like PASS);
END

---

Hi there, I changed a few things in your code, unfortunately untill you supply me with your error I cant give you a full explanation on what went wrong, but there are quite a few syntax errors in that snippit of code.

So lets begin, first things first you SQL query, when you want to obtain perfect matches try and avoid the "like" keyword, the way you did it is fine, by try to stick to = as if you accidentilly add a wild card character your login script would not work successfully.

Lastly the extra variable creation is unnecessary, when you have a procedure and you specify an IN or OUT variables they are already predefined for you, so all you need to do is a select into the variable and nothing else is needed, also please remember to specify the IN and OUT types, as this just allows for better readability.

Hope this helps,

Kind Regards

and lastly, the code xD

CREATE PROCEDURE CheckLogin(IN Login varchar(25), IN PASS varchar(10),OUT ReturnValue INT)
BEGIN
select count(*) INTO ReturnValue from accounts where Login = ID and Pass = PASS;
END

Thx's, I'll check it out and let ya know :P

One last addon my apologies that i missed it xD

Your IN variable Login was not matching to ID, so that just needs to be login as well, the following code snippit should work. Sorry about that

CREATE PROCEDURE CheckLogin(IN Login varchar(25), IN PASS varchar(10),OUT ReturnValue INT)
BEGIN
select count(*) INTO ReturnValue from accounts where Login = Login and Pass = PASS;
END

Im not sure on the impact of having the variable name the same as the column name, and dont have mysql with me currently to check, will check later, unless you beat me to it. If it still doesnt want to work try the following rather

CREATE PROCEDURE CheckLogin(IN szLogin varchar(25), IN szPASS varchar(10),OUT ReturnValue INT)
BEGIN
select count(*) INTO ReturnValue from accounts where Login = szLogin and Pass = szPASS;
END

Yea, first script you gave me tells me, Unknown Column 'test' in 'field list'

Edit Both say:
I input, "test, test, 0" gives me "1054 - Unknown column 'test' in 'field list'.

Edit2nd:
I tried
"
BEGIN
set Username = szLogin;
set Pwd = szPASS;
select count(*) INTO ReturnValue from accounts where Username = Login and Pwd = Pass;
END
"
Still didnt work xD

Hi, sorry again, i seem to be getting confused, the first time you posted your query was

select count(*) as ReturnVal from accounts where Login like ID and Pwd like PASS

however your latest post shows it as such :

select count(*) INTO ReturnValue from accounts where Username = Login and Pwd = Pass;

...your column names changes. Could you please do a describe (/dt accounts) for me and post the output so i can get the column names. In the where clause the first name relates to the column name in the table and the second relates to the argument being passed.

Use this one again as is :

CREATE PROCEDURE CheckLogin(IN szLogin varchar(25), IN szPASS varchar(10),OUT ReturnValue INT)
BEGIN
select count(*) INTO ReturnValue from accounts where Login=szLogin and Pass=szPASS;
END

however first run this query :

select count(*) from accounts where Login = 'test' and Pass = 'test';

And what is the output? If this causes an error either your table name or column names are incorrect. Also next time you run, can you please copy and paste the error mysql gives you.

Kind Regards, hope to hear from you soon.

Its,

Code:

---

select count(*) from accounts where ID = 'test' and PASS = 'test';

---

And It returned 1.

test & test is a real test login in the tables, and ID & PASS are the columns.

EDIT:
When I put into Parameter

Code:

---

'test','test', 0

---

1414 - OUT or INOUT argument 3 for routine ecafe. CheckLogin is not a variable or NEW pseudo-variable in BEFORE trigger.

but when I put

Code:

---

test, test, 0

---

1054 - Unknown Column 'test' in 'field list'.

My PROCEDURE

Code:

---

BEGIN
Declare ReturnValue int;
set ReturnValue =0;
select count(*) INTO ReturnValue from accounts where szLogin=ID and szPASS=PASS;
END

---

PS, With or without Decalring and setting I still get the same issue. So I just left it there.

Just one quick question i need, are you calling this from PHP, because PHP: MySQL (PDO) - Manual says that pdo_mysql uses server side prepare statements or emulates they. But server side prepared statements do not support CALL yet: MySQL AB :: MySQL 5.1 Reference Manual :: 11.7 SQL Syntax for Prepared Statements

So if thats the case you will need to do the procedure inline in your code, I will gladly assist with this as well, also please test the proc by replacing this line of code in your procedure "select count(*) INTO ReturnValue from accounts where szLogin=ID and szPASS=PASS;" with this line of code "select count(*) from accounts where ID = 'test' and PASS = 'test';" then call it again from withing mysql, and not from withing a php page (if you are using a php page) please post all responses and outputs.

Kind Regards

c# program, I'm trying to make a login via mysql. And I got a CheckLogin script thats calling it.I think I might just do the script via c#/mysql except just mysql. xD