SQL Server to MySQL Stored Procedure

[Manny_56]

Hi,

Although I am familiar with SQL, I am new to both SQL Server and MySQL and I have never worked with Stored Procedures or Functions before. I need help in converting the following Stored Procedure from SQL Server to MySQL 5.0 for one of the projects I'm working on (tried looking through MySQL manuals to no avail):

CREATE PROCEDURE account_Login

@ScreenName AS VARCHAR(50),
@Password AS VARCHAR(50)

AS

SET NOCOUNT ON

DECLARE @GoodLogin AS INTEGER

SET @GoodLogin = (SELECT UserID
FROM Users
WHERE ScreenName = @ScreenName
AND [Password] = @Password
AND Status <> 0)

IF @GoodLogin IS NOT NULL
BEGIN
UPDATE Users SET LastLogin = GETDATE() WHERE UserID = @GoodLogin
END

SELECT *
FROM Users
WHERE ScreenName = @ScreenName
AND [Password] = @Password
AND Status <> 0

RETURN

SET NOCOUNT OFF


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Any help will be greatly appreciated.

Regards,

Manny

[codeexploiter]

I have no working experience in MySQL but i've tried to convert your MS SQL Server stored procedure into a MYSQL stored procedure. Please checkout the following code

CREATE PROCEDURE account_Login (IN ScrName VARCHAR(50),IN Passwd AS VARCHAR(50) )
BEGIN
DECLARE GoodLogin INT
SELECT UserID INTO GoodLogin FROM Users WHERE ScreenName = ScrName AND Password = Passwd AND Status <> 0;
IF GoodLogin IS NOT NULL THEN
	UPDATE Users SET LastLogin = CURDATE() WHERE UserID = GoodLogin;
END IF;

SELECT * FROM Users WHERE ScreenName = ScrName AND Password = Passwd AND Status <> 0;
END

Just to avoid the confusion i've used different names for the variables inside the procedure and the table columns.

[Manny_56]

Thank you for your response. I've just tried the code sample you've provided (removed AS after Passwd on the first line) and I got an error on line 3. So I tried adding a semicolon at the end of the declare statement. It still gave me an error. I then removed the "INT" and the semicolon and the error moved to line 4 starting at "SELECT...."

I compared the statement to the manual but everything seems fine. I'm not sure if I should just build the procedure into my application as it might be far easier (only problem is, I've got about 150 procedures that need adding) - I've been working with this procedure for over 48hrs now without doing anything else.

Any other ideas welcome. Thanks again for your help.

Manny

[neesco11@yahoo.com]

Hello Manny,
Did you find a solution for this? I have some info and we can exchange if you want. Please email me at: rajaprathap at juble dot com

[Master_script_maker]

try:

CREATE PROCEDURE account_Login (IN ScrName VARCHAR(50),IN Passwd AS VARCHAR(50) )
BEGIN
DECLARE GoodLogin INT(number of digits)
SELECT UserID INTO GoodLogin FROM Users WHERE ScreenName = ScrName AND Password = Passwd AND Status <> 0;
IF GoodLogin IS NOT NULL THEN
	UPDATE Users SET LastLogin = CURDATE() WHERE UserID = GoodLogin;
END IF;

SELECT * FROM Users WHERE ScreenName = ScrName AND Password = Passwd AND Status <> 0;
END