MySQL Forums
Forum List  »  Microsoft SQL Server

Convert T-SQL Stored Procedure to MySQL
Posted by: thanh nguyen
Date: February 06, 2006 09:01AM

Hi,

Could anyone please help to to convert this T-SQL stored procedure to MySQL store procedure?Is there any tool to do this task? I've many stored procedures to be converted!
-----------
CREATE PROCEDURE TT_AddProject
(
@Name nvarchar(50),
@Description nvarchar(1024),
@ManagerUserID int,
@EstCompletionDate datetime,
@EstDuration int,
@Members nvarchar(2000),
@Categories nvarchar(4000)
)
AS

DECLARE @Error int
DECLARE @ProjectID int
DECLARE @TempString varchar(4000)
DECLARE @Temp nvarchar(4000)
DECLARE @Count int
DECLARE @TempTable TABLE(UserID int PRIMARY KEY)
DECLARE @InnerTemp nvarchar(50)
DECLARE @CatName varchar(20)
DECLARE @Abbrev varchar(5)
DECLARE @Duration decimal(10,2)
DECLARE @InnerCount int

BEGIN TRANSACTION

INSERT INTO TT_Projects
(
[Name],
[Description],
ManagerUserID,
EstCompletionDate,
EstDuration,
CreationDate
)
VALUES
(
@Name,
@Description,
@ManagerUserID,
@EstCompletionDate,
@EstDuration,
getdate()
)

SET @Error = @@ERROR
IF @Error != 0 GOTO ERROR_HANDLER

SET @ProjectID = @@Identity

SET @TempString = @Members

SET @Count = CHARINDEX(',', @TempString)

WHILE @Count > 0
BEGIN
SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))
INSERT INTO @TempTable VALUES(CAST(@Temp AS int))
SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
SET @Count = CHARINDEX(',', @TempString)
END

INSERT INTO @TempTable VALUES(CAST(@TempString AS int))

INSERT INTO TT_ProjectMembers
SELECT @ProjectID, UserID FROM @TempTable

SET @Error = @@ERROR
IF @Error != 0 GOTO ERROR_HANDLER

SET @TempString = @Categories

SET @Count = CHARINDEX(';', @TempString)
WHILE @Count > 0
BEGIN
SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))

SET @InnerCount = CHARINDEX(',', @Temp)

SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
SET @CatName = @InnerTemp

SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
SET @InnerCount = CHARINDEX(',', @Temp)

SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
SET @Abbrev = @InnerTemp

SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
SET @InnerCount = CHARINDEX(',', @Temp)

SET @Duration = CAST(@Temp AS int)

INSERT INTO TT_Categories
(
ProjectID,
[Name],
Abbreviation,
EstDuration
)
VALUES
(
@ProjectID,
@CatName,
@Abbrev,
@Duration
)

SET @Error = @@ERROR
IF @Error != 0 GOTO ERROR_HANDLER

SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
SET @Count = CHARINDEX(';', @TempString)

END

set @Temp = @TempString
SET @InnerCount = CHARINDEX(',', @Temp)

SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
SET @CatName = @InnerTemp

SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
SET @InnerCount = CHARINDEX(',', @Temp)

SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
SET @Abbrev = @InnerTemp

SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
SET @InnerCount = CHARINDEX(',', @Temp)

SET @Duration = CAST(@Temp AS int)

INSERT INTO TT_Categories
(
ProjectID,
[Name],
Abbreviation,
EstDuration
)
VALUES
(
@ProjectID,
@CatName,
@Abbrev,
@Duration
)

SET @Error = @@ERROR
IF @Error != 0 GOTO ERROR_HANDLER

COMMIT TRANSACTION

SELECT @ProjectID AS ProjectID

ERROR_HANDLER:
IF @@TRANCOUNT != 0 ROLLBACK TRANSACTION
RETURN @Error

GO
------------

Options: ReplyQuote


Subject
Written By
Posted
Convert T-SQL Stored Procedure to MySQL
February 06, 2006 09:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.