Converting MS SQL Stored Procedure to MySQL

[ICTECH]

Can anyone help me convert these two MS SQL Stored Procedure to MySQL Stored Procedure? Not sure how to convert them..

ALTER PROCEDURE InvalidCredentialsLog_Insert
(
   @ApplicationName    nvarchar(256),
   @UserName           nvarchar(256),
   @Password           nvarchar(128),
   @IPAddress          varchar(15)
)
AS

   DECLARE @IsApproved BIT, @IsLockedOut BIT

   -- Read in the user's @UserID, @IsApproved & @IsLockedOut values, if they exist
   SELECT @IsApproved = m.IsApproved, @IsLockedOut = m.IsLockedOut FROM Applications a, Users u, Membership m WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND u.ApplicationId = a.ApplicationID AND Lower(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

   -- Insert a new record into InvalidCredentialsLog
  INERT INTO InvalidCredentialsLog(UserName, Password, IsApproved, IsLockedOut, IPAddress) VALUES(@UserName, @Password, @IsApproved, @IsLockedOut, @IPAddress)

ALTER PROCEDURE InvalidCredentialsLog_Summary
AS
     DECLARE @RightNow DATETIME
     SET @RightNow = getdate()

   -- Returns number of entries in past 24 hrs, week, month and total
   SELECT 
         -- Past 24 hrs
         (SELECT Count(*) FROM InvalidCredentialsLog WHERE LoginAttemptDate >= DateAdd(hh, -24, @RightNow)) AS Last24Hrs,
         -- Last Week
         (SELECT Count(*) FROM InvalidCredentialsLog WHERE LoginAttemptDate >= DateAdd(hh, -2, @RightNow)) AS LastWeek,
         -- Last Month
         (SELECT Count(*) FROM InvalidCredentialsLog WHERE LoginAttemptDate >= DateAdd(m, -1, @RightNow)) AS LastMonth,
         -- Total
         (SELECT Count(*) FROM InvalidCredentialsLog) AS Total,

Thanks..

 

[DonQuichote]

I suggest you read the fine manual about stored routines in MySQL. If you use the command-line client, you will need to change the delimiter with the DELIMITER command (which is NOT an SQL statement, but a command within the command-line client).
Not all variables carry a '@' in mysql. The assignments in a SELECT have an extra colon:

SELECT @SomeVar:=SomeColumn WHERE ...

or

SELECT SomeColumn INTO @SomeVar WHERE ...

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[ICTECH]

Thanks for your reply DonQuichote.

But can you help me translate those two procedures into MySQL procedures?

SELECT @IsApproved = m.IsApproved, @IsLockedOut = m.IsLockedOut FROM Applications a, Users u, Membership m WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND u.ApplicationId = a.ApplicationID AND Lower(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

Would become...

SELECT m.IsApproved INTO @IsApproved, m.IsLockedOut INTO @IsLockedOut  FROM Applications a, Users u, Membership m WHERE a.LoweredApplicationName = LOWER(@ApplicationName) AND u.ApplicationId = a.ApplicationID AND u.LoweredUserName = Lower(@UserName) AND u.UserId = m.UserId