Convert T-SQL Stored Procedure to MySQL
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
------------