Help T-SQL to MySQL
Hi,
Could anyone please convert this T-SQl stored procedure to MySQL stored procedure?
This is dynamic sql?
Please give me some ideas
thanks
Thanh
---------------------------------------------
CREATE PROCEDURE TT_ListUserTimeSummary
(
@ManagerUserID int,
@UserIDList nvarchar(512),
@StartDate datetime,
@EndDate datetime
)
AS
DECLARE
@sSqlString nvarchar(1024),
@sSubSql nvarchar(1024),
@RoleID int
SELECT @RoleID = RoleID
FROM TT_Users
WHERE UserID = @ManagerUserID;
IF (@RoleID = 1)
BEGIN
SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
SET @sSqlString = @sSqlString + ' FROM TT_EntryLog EL Inner Join TT_Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @UserIDList + ')'
SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2 GROUP BY U.UserID, U.UserName'
END
ELSE IF (@RoleID = 2)
BEGIN
SET @sSubSql = 'SELECT PM.UserID FROM TT_Projects P INNER JOIN TT_ProjectMembers PM'
SET @sSubSql = @sSubSql + ' ON P.ProjectID = PM.ProjectID WHERE P.ManagerUserID = @3 AND PM.UserID IN (' + @UserIDList + ')'
SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
SET @sSqlString = @sSqlString + ' FROM TT_EntryLog EL Inner Join TT_Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @sSubSql + ')'
SET @sSqlString = @sSqlString + ' AND EL.ProjectID IN (SELECT ProjectID From TT_Projects Where ManagerUserID = @3) '
SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2 GROUP BY U.UserID, U.UserName'
END
ELSE
SET @sSqlString = 'SELECT U.UserID AS TotalHours, U.UserID, U.UserName From TT_Users U Where 1=0'
EXEC sp_executesql @sSqlString, N'@1 datetime, @2 datetime, @3 int', @StartDate, @EndDate, @ManagerUserID
GO
-------------------------------------