Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2006
    Posts
    2

    Unanswered: can you help me please

    Hi
    what is the problem in this code please

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

    and this the messege

    Msg 170, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 21
    Line 21: Incorrect syntax near 'TRY'.
    Msg 156, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 28
    Incorrect syntax near the keyword 'END'.
    Msg 156, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 33
    Incorrect syntax near the keyword 'END'.
    Msg 170, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 51
    Line 51: Incorrect syntax near 'TRY'.
    Msg 170, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 56
    Line 56: Incorrect syntax near 'TRY'.
    Msg 170, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 61
    Line 61: Incorrect syntax near 'CATCH'.
    Msg 156, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 64
    Incorrect syntax near the keyword 'END'.
    Msg 156, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 72
    Incorrect syntax near the keyword 'END'.

    can you help me and told me what is the error and how to fix it
    i am using SQL Server 2005 Express who comes with VS.2005
    thank you

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use of the VALUES syntax implies constants, you're using functions like NewID() and GetDate() in the lists, which SQL doesn't find amusing at all... I would change the VALUES clause to a SELECT statement.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •