Unable to convert a stored procedure
Hi, I try to convert a MSSQL 2005 stored rpocedure to MySQL.
This is the code I want to convert (MSSQL 2005):
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[aspnet_Membership_FindUsersByEmail]
@ApplicationName nvarchar(256),
@EmailToMatch nvarchar(256),
@PageIndex int,
@PageSize int
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN 0
-- Set the page bounds
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
-- Create a temp table TO store the select results
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId uniqueidentifier
)
-- Insert into our temp table
IF( @EmailToMatch IS NULL )
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.Email IS NULL
ORDER BY m.LoweredEmail
ELSE
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.LoweredEmail LIKE LOWER(@EmailToMatch)
ORDER BY m.LoweredEmail
SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate,
m.LastLoginDate,
u.LastActivityDate,
m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,
m.LastLockoutDate
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND
p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
ORDER BY m.LoweredEmail
SELECT @TotalRecords = COUNT(*)
FROM #PageIndexForUsers
RETURN @TotalRecords
END
This is one of my tries (MySQL):
USE database;
DROP PROCEDURE IF EXISTS aspnet_Membership_FindUsersByEmail;
DELIMITER $$
CREATE PROCEDURE aspnet_Membership_FindUsersByEmail
(
ApplicationName nvarchar(256),
EmailToMatch nvarchar(256),
PageIndex int,
PageSize int
)
BEGIN
DECLARE ApplicationId int(16) DEFAULT NULL;
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LoweredApplicationName = LOWER(@ApplicationName);
IF (@ApplicationId IS NOT NULL) THEN
-- Set the page bounds
DECLARE PageLowerBound int;
DECLARE PageUpperBound int;
DECLARE TotalRecords int;
SET @PageLowerBound = @PageSize * @PageIndex;
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound;
-- Create a temp table TO store the select results
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId int(16)
)
IF( @EmailToMatch IS NULL ) THEN
INSERT INTO @PageIndexForUsers (UserId)
SELECT u.UserId
FROM aspnet_Users u, aspnet_Membership m
WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.Email IS NULL
ORDER BY m.LoweredEmail;
ELSE
INSERT INTO @PageIndexForUsers (UserId)
SELECT u.UserId
FROM aspnet_Users u, aspnet_Membership m
WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.LoweredEmail LIKE LOWER(@EmailToMatch)
ORDER BY m.LoweredEmail;
END IF;
SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate,
m.LastLoginDate,
u.LastActivityDate,
m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,
m.LastLockoutDate
FROM aspnet_Membership m, aspnet_Users u, @PageIndexForUsers p
WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND
p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
ORDER BY m.LoweredEmail;
SELECT @TotalRecords = COUNT(*)
FROM @PageIndexForUsers;
SELECT @TotalRecords;
END IF;
END;
$$
DELIMITER ;
ERROR:
#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 PageLowerBound int;
DECLARE PageUpperBound int;
DECLARE TotalRecor' at line 14
I hope that someone can help me
Edited 1 time(s). Last edit at 04/06/2010 01:03PM by jonathan touchette.