Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Setting up a new job

    Hi

    I'm trying to set up a new job to update a field in the table, I've managed to get the select syntax to work, but when I added an IF statement and Update syntax it didn't like it and the following error was shown:-

    Server: Msg 156, Level 15, State 1, Line 9
    Incorrect syntax near the keyword 'BEGIN'.
    This is the syntax I'm trying to use for the job:-

    Code:
    USE EmployerEngagement
    
    IF (SELECT On_Stop
        FROM tblEmployer LEFT OUTER JOIN tblWP_Details
        ON tblEmployer.Emp_ID = tblWP_Details.Emp_ID LEFT OUTER JOIN tblVetting
        ON tblWP_Details.Record_ID = tblVetting.Record_ID
        WHERE tblEmployer.On_Stop = 0
        AND tblVetting.Next_Vett_Date <= GETDATE())
    BEGIN
         UPDATE tblEmployer
         SET On_Stop = 1
    END
    Basically I just want to change the On_Stop value from 0 to 1 if the Next_Vett_Date is before or on todays date.

    Am I using the wrong syntax for this?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Posts
    269
    'if' statement checks for true or false.
    Code:
    IF exists(SELECT On_Stop
        FROM tblEmployer LEFT OUTER JOIN tblWP_Details
        ON tblEmployer.Emp_ID = tblWP_Details.Emp_ID LEFT OUTER JOIN tblVetting
        ON tblWP_Details.Record_ID = tblVetting.Record_ID
        WHERE tblEmployer.On_Stop = 0
        AND tblVetting.Next_Vett_Date <= GETDATE())
    BEGIN
         UPDATE tblEmployer
         SET On_Stop = 1
    END
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    your if statement is not a boolean condition

    why not just do this...


    UPDATE tblEmployer SET On_Stop = 1
    FROM tblEmployer
    LEFT OUTER JOIN tblWP_Details
    ON tblEmployer.Emp_ID = tblWP_Details.Emp_ID
    LEFT OUTER JOIN tblVetting
    ON tblWP_Details.Record_ID = tblVetting.Record_ID
    WHERE tblEmployer.On_Stop = 0
    AND tblVetting.Next_Vett_Date <= GETDATE()

    EDIT: SNIPED. damn it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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