Hi,
I saw you post today, sorry for delay.
SqlTran, our database translator, handles SQL Server to MySQL easily - and translates all objects. You can learn more about it at
www.sqltran.com.
So, when I copied and pasted your SQL Server procedure to SqlTran, I got a nicely translated procedure out. I changed ALTER statement to CREATE as you don't have it in MySQL database created yet.
The output:
CREATE PROCEDURE aspnet_Membership_FindUsersByEmail
(ApplicationName VARCHAR(256) CHARACTER SET UTF8,
EmailToMatch VARCHAR(256) CHARACTER SET UTF8,
PageIndex INT,
PageSize INT)
proc:BEGIN
DECLARE ApplicationId CHAR(38);
DECLARE PageLowerBound INT;
DECLARE PageUpperBound INT;
DECLARE TotalRecords INT;
SELECT
NULL
INTO ApplicationId;
SELECT
ApplicationId
INTO ApplicationId
FROM
aspnet_Applications
WHERE
LOWER(ApplicationName) = LoweredApplicationName;
IF (ApplicationId IS NULL) THEN
LEAVE proc;
END IF;
SET PageLowerBound = PageSize * PageIndex;
SET PageUpperBound = PageSize - 1 + PageLowerBound;
CREATE TEMPORARY TABLE PageIndexForUsers(
IndexId INT NOT NULL AUTO_INCREMENT,
UserId CHAR(38)
);
IF (EmailToMatch IS NULL) THEN
INSERT INTO PageIndexForUsers(UserId)
SELECT
u.UserId
FROM
aspnet_Users AS u, aspnet_Membership AS 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 AS u, aspnet_Membership AS 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 AS m, aspnet_Users AS u, PageIndexForUsers AS p
WHERE
u.UserId = p.UserId AND u.UserId = m.UserId AND p.IndexId >= PageLowerBound AND p.IndexId <= PageUpperBound
ORDER BY
m.LoweredEmail;
SELECT
COUNT(*)
INTO TotalRecords
FROM
PageIndexForUsers;
LEAVE proc;
END;
I am not sure how to paste code and preserve formatting, so I simply pasted it as text. In SqlTran, generated translation is nicely formatted, of course.
I hope this helps. SqlTran actually does many transformations when translating data, emulates missing features, transforms functions, and much more...
Take a look at
www.sqltran.com.
Damir Bulic
Spectral Core