CREATE  PROCEDURE usp_InsertTimeSheet
(
   @TimeSheetID      UNIQUEIDENTIFIER,
   @UserID           UNIQUEIDENTIFIER,
   @WeekEndingDate   DATETIME
)
AS

DECLARE @GroupID UNIQUEIDENTIFIER,
        @ProjectID UNIQUEIDENTIFIER,
        @TimeSheetDate DATETIME,
        @i TINYINT

-- Get the GroupID that the user belongs to
SELECT @GroupID = GroupID FROM Users WHERE UserID = @UserID

DECLARE Project_Cursor CURSOR FOR SELECT ProjectID 
   FROM GroupProjects WHERE GroupID = @GroupID

BEGIN TRANSACTION
   BEGIN TRY
      -- Insert the time sheet
      INSERT INTO TimeSheets
         (TimeSheetID, UserID, WeekEndingDate, Submitted, ApprovalDate,
         ManagerID, LastUpdateDate)
         VALUES(@TimeSheetID, @UserID, @WeekEndingDate, 0, NULL,
         NULL, GETDATE())
   END TRY
   BEGIN CATCH
      ROLLBACK TRANSACTION
      RAISERROR('Insert into TimeSheets failed.',18,1)
      RETURN
   END CATCH

   -- Set the initial time sheet date to the beginning of the week
   SET @TimeSheetDate = @WeekEndingDate - 4

   -- Set up a loop to insert time sheet items for 5 days
   SET @i = 1
   WHILE (@i < 6)

      BEGIN
      -- Open the cursor
      OPEN Project_Cursor

      -- Get the first row of data from the cursor into our variable
      FETCH NEXT FROM Project_Cursor INTO @ProjectID

      WHILE @@FETCH_STATUS = 0
         BEGIN
         BEGIN TRY
            -- Insert the time sheet item
            INSERT INTO TimeSheetItems
            (TimeSheetItemID, TimeSheetID, ProjectID, Hours, TimeSheetDate)
               VALUES(NEWID(), @TimeSheetID, @ProjectID, 0, @TimeSheetDate)
         END TRY
         BEGIN CATCH
            ROLLBACK TRANSACTION
            RAISERROR('Insert into TimeSheetItems failed.',18,1)
            RETURN
         END CATCH
         -- Get the next row of data from the cursor into our variable
         FETCH NEXT FROM Project_Cursor INTO @ProjectID
         END

      CLOSE Project_Cursor

      -- Increment the date by one day
      SET @TimeSheetDate = @TimeSheetDate + 1
      -- Increment the loop counter by one
      SET @i = @i + 1
      END

-- Deallocate cursor
DEALLOCATE Project_Cursor

-- Commit all inserts
COMMIT TRANSACTION