ALTER PROCEDURE [dbo].[Pr_TimeCardReport2]
@EM VARCHAR(MAX),
@SD DATETIME,
@ED DATETIME
AS
BEGIN
DECLARE @Sql VARCHAR(8000)
DECLARE @Index INT
SET @Index = 0
SET @Sql = 'SELECT TC.ID, TC.Start, TC.Finish, TC.EmpMail,
(SELECT OUTTime FROM TimeCardBreaks WHERE BreakType = ''Lunch''
AND TCID = TC.ID) [L_Out],
(SELECT INTime FROM TimeCardBreaks WHERE BreakType = ''Lunch''
AND TCID = TC.ID) [L_In], '
WHILE (@Index < 10)
BEGIN
SET @Sql = @Sql + '(SELECT OUTTime FROM TimeCardBreaks TCB WHERE BreakType = ''Smoke''
AND TCID = TC.ID
AND ' + CAST(@Index AS VARCHAR) + ' = (SELECT COUNT(Id) FROM TimeCardBreaks WHERE BreakType = ''Smoke'' AND TCID = TC.ID
AND ID < TCB.ID)) [S' + CAST(@Index+1 AS VARCHAR) + '_Out],
(SELECT INTime FROM TimeCardBreaks TCB WHERE BreakType = ''Smoke''
AND TCID = TC.ID
AND ' + CAST(@Index AS VARCHAR) + ' = (SELECT COUNT(Id) FROM TimeCardBreaks WHERE BreakType = ''Smoke'' AND TCID = TC.ID
AND ID < TCB.ID)) [S' + CAST(@Index+1 AS VARCHAR) + '_In], '
SET @Index = @Index + 1
END
SET @Sql = SUBSTRING(@Sql, 1, LEN(@Sql)-1)
SET @Sql = @Sql + ' FROM TimeCard TC WHERE EmpMail = ''' +@EM+ ''' AND Start >= ''' + CAST(@SD AS VARCHAR)+ ''' AND Finish <= ''' + CAST(@ED AS VARCHAR) + ''''
EXEC (@Sql)
END