usp_inserttimesheet.sql

Beginning VB.NET DatabasesAll_Code.rar

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
   -- Insert the time sheet
   INSERT INTO TimeSheets
      (TimeSheetID, UserID, WeekEndingDate, Submitted, ApprovalDate,
      ManagerID, LastUpdateDate)
      VALUES(@TimeSheetID, @UserID, @WeekEndingDate, 0, NULL,
      NULL, GETDATE())
   IF @@ERROR <> 0 
      BEGIN
      ROLLBACK TRANSACTION
      RAISERROR('Insert into TimeSheets failed.',18,1)
      RETURN
      END

   -- 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
         -- Insert the time sheet item
         INSERT INTO TimeSheetItems
         (TimeSheetItemID, TimeSheetID, ProjectID, Hours, TimeSheetDate)
            VALUES(NEWID(), @TimeSheetID, @ProjectID, 0, @TimeSheetDate)
         IF @@ERROR <> 0 
            BEGIN
            ROLLBACK TRANSACTION
            RAISERROR('Insert into TimeSheetItems failed.',18,1)
            RETURN
            END
         -- 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