May 31, 2011 at 2:40 pm
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;
May 31, 2011 at 3:34 pm
If we look at just this first part:
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 ...
You are checking IF EXISTS with this clause "where (intAnswer = 1 or intAnswer = 2) and pt.strSSN = @SSN)", but you are not using that in the UPDATE. Wouldn't you want to update the same rows you are checking for existence?
Also, you can shorten that clause to "where intAnswer in (1,2) and pt.strSSN = @SSN"
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 1, 2011 at 12:10 am
Hi
You can use MERGE statement, in sql 2008R2. This may be helpful.
http://technet.microsoft.com/en-us/library/bb510625.aspx
Regards
Siva Kumar J.
June 2, 2011 at 12:14 pm
I would use a MERGE statement as well. Something along the lines like this:
MERGE tblPermTask AS pt
USING
(SELECT
ed1.intQuestionId,
ed1.intSRpAttendID,
q.strShortTask,
ed1.dtLogged,
ed1.intPersonnelId,
ed1.strRemarks,
Case WHEN ed1.strRemarks = '' THEN '' else 'SRP Notes' + ed1.strRemarks end AS strRemarks
From tblSRPEventData as ed INNER JOIN
tblSRPQuestion as q on q.intQuestionId = ed1.intQuestionId
Where intAnswer = 1 or intanswer = 2 and intPersonnelID = @SSN and intSRPAttendId in (Select intSRPAttendId from tblSRPAttendance where intEventId = @EventId)
)
AS ed ON ed.intQuestionId = pt.intQuestionId and ed.intPersonnelId = pt.strssn
WHEN MATCHED THEN
UPDATE SET
pt.strNotes = pt.strnotes + ed.strRemarks
WHEN NOT MATCHED THEN
INSERT(intQuestionId, intSrpAttendID, strTaskName, dtFound, strSSN, strNotes)
VALUES ( ed.intQuestionId,ed.intSRpAttendID,q.strShortTask,ed.dtLogged,ed.intPersonnelId,ed.strRemarks)
;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply