MySQL Forums
Forum List  »  Microsoft SQL Server

Help T-SQL to MySQL
Posted by: thanh nguyen
Date: February 17, 2006 09:20AM

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
-------------------------------------

Options: ReplyQuote


Subject
Written By
Posted
Help T-SQL to MySQL
February 17, 2006 09:20AM
February 17, 2006 10:02AM


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.