Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2008
    Posts
    5

    Red face Unanswered: If exists Update else Insert

    I am geting some crazy results on my stored procedure I created, cannot seem to get the sytax correct for if a record exists on 2 columns then do an update else insert.

    What I have is a table of questions that I insert and create permenant tasks in another table. It all goes off of what the answer is 0, 1, 2

    If intAnswer is a 0 I want to update the notes and close out the PermTask,
    If intanswer is a1 or 2 and it does not exist in tblPermTask i want to insert else update the notes for those questions of an individual.

    I guess at this point I do not know if i am doing any of this correctly.

    ALTER Proc [dbo].[sp_CreateTasks] (@SSN as varchar(9), @EventId as integer)
    AS

    --Insert all records that have answer of 1 or 2 into tblPermTask
    IF Exists(Select pt.intQuestionId from tblPermTask pt INNER JOIN tblsrpeventdata ed on ed.intquestionId = pt.intquestionid and ed.intpersonnelid = pt.strssn
    where (intAnswer = 1 or intAnswer = 2) and pt.strSSN = @SSN)
    Update pt
    set
    pt.strNotes = pt.strnotes + Case WHEN ed.strRemarks = '' THEN '' else 'SRP Notes' + ed.strRemarks end
    From tblPermTask as pt INNER JOIN
    tblSRPEventData as ed on ed.intQuestionId = pt.intQuestionId and ed.intPersonnelID = pt.strSSN
    ELSE
    Insert tblPermTask (intQuestionId, intSrpAttendID, strTaskName, dtFound, strSSN, strNotes)
    Select ed.intQuestionId,
    ed.intSRpAttendID,
    q.strShortTask,
    ed.dtLogged,
    ed.intPersonnelId,
    ed.strRemarks
    From tblSRPEventData as ed INNER JOIN
    tblSRPQuestion as q on q.intQuestionId = ed.intQuestionId
    Where intAnswer = 1 or intanswer = 2 and intPersonnelID = @SSN and intSRPAttendId in (Select intSRPAttendId from tblSRPAttendance where intEventId = @EventId)

    --Close out all existing tasks that where corrected in the Questions IF Exists(Select pt.intQuestionId from tblPermTask pt INNER JOIN tblsrpeventdata ed on ed.intquestionId = pt.intquestionid and ed.intpersonnelid = pt.strssn
    where intAnswer = 0 and pt.strSSN = @SSN)
    Update pt
    set
    pt.strNotes = pt.strnotes + ' SRP EVENT NOTES ' + ed.strRemarks,
    pt.dtCompleted = GETDATE(),
    pt.strClosedby = 'SRP Event ' + cast(@EventId as varchar(max)),
    pt.bitLocked = NULL
    From tblPermTask as pt INNER JOIN
    tblSRPEventData as ed on ed.intQuestionId = pt.intQuestionId and ed.intPersonnelID = pt.strSSN;

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Have you looked at the MERGE statement?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Mar 2008
    Posts
    5

    No

    No i guess i have not looked at it, or I guess even thought about it, i don't use enough advanced t-sql to really be knowledgeable on all the commands.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    MERGE combines an INSERT, and UPDATE and a DELETE all in one statement (you don't have to use all three actions. It can be a bit tedious to learn, but worth it in the end. Simplifies your code.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Mar 2008
    Posts
    5
    Ok, If i hear you correctly you are saying that I can use Merge for all 3 instances of the intAnswer 0(Update), 1 and 2 (Update if exists else insert)

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Don't/didn't have time to look into the details of your SP, but I saw that you had a criteria, and you were deciding to INSERT or UPDATE based upon that criteria, which makes you a candidate for the MERGE statement.

    Can't guarantee it will cover all your bases . . . you're going to have to discover that.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd second the suggestion to use MERGE. You'll have a slight learning curve, so try it on a simple data set first. But its a valuable tool to have in your box.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Mar 2008
    Posts
    5

    Red face

    ok, I came up with the following. However as soone as I changed to this new procedure which works fine now my aspx page that sends the 2 variables now times out. If I switch back to my old code it works fine but the inserting, updating is wrong. So what makes this time out my page.

    ALTER Proc [dbo].[sp_CreateTasks] (@SSN as varchar(9), @EventId as integer)
    AS

    --Insert all the records into tblSRpEventDataHistory
    While 1 = 1
    Begin
    Delete From tblSRPEventData
    OutPut deleted.intPersonnelID, deleted.intSRPAttendId, deleted.intQuestionId, deleted.intAnswer, deleted.strRemarks,
    deleted.strDocDate, deleted.dtLogged, deleted.strLogged
    INTO tblSrpEventDataHistory
    (intPersonnelId, intSRpAttendID, intQuestionId, intAnswer, strRemarks, strDocDate, dtLogged, strLogged)
    WHERE intPersonnelID = @SSN and intSRPAttendId in (Select intSRPAttendId from tblSRPAttendance where intEventId = @EventId)
    END

    --Insert all the records into tblSRpEventDataHistory
    Merge into tblPermTask as pt
    USING (Select ed.intQuestionId,
    ed.intAnswer,
    ed.intSRpAttendID,
    q.strShortTask,
    ed.dtLogged,
    ed.intPersonnelId,
    ed.strRemarks
    From tblSRPEventData as ed INNER JOIN tblSRPQuestion as q on q.intQuestionId = ed.intQuestionId where ed.intAnswer in (1,2) and
    intPersonnelid = @SSN and intSrpAttendId in (Select intSrpAttendId from tblSrpAttendance where intEventId = @EventId)) as src
    ON pt.intQuestionId = src.intQuestionID AND pt.strSSN = src.intPersonnelId
    WHEN MATCHED THEN
    UPDATE SET
    pt.strNotes += Case WHEN src.strRemarks = '' THEN '' else ' -- SRP Notes -- ' + src.strRemarks END
    WHEN NOT MATCHED THEN
    INSERT (intQuestionId, intSrpAttendID, strTaskName, dtFound, strSSN, strNotes)
    VALUES (src.intQuestionId, src.intSRpAttendID, src.strShortTask, src.dtLogged, src.intPersonnelId, src.strRemarks);

Posting Permissions

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