I have a stored procedure that i pass 2 variables into when a person checks out. what it does is take all ther questions and insert them into a history table and then creates tasks fro them to fix in a task table. My problem is that when inserting into the task table I am creating dual tasks and what they want to happen if a task doe snot exist, if it does they just want to append the new notes to existing notes. The 2 things that match in both tables is the intquestionId and ssn. I cannot pass the questionId into the sp because of the process. Is there a way to block it in the stored procedure.
attached is the table layouts with id's.
I tried to do if exists, but it did not work.
ALTER Proc [dbo].[sp_CreateTasks] (@SSN as varchar(9), @EventId as integer)
--Insert all the records into tblSRpEventDataHistory
Insert tblSrpEventDataHistory (intPersonnelId, intSRpAttendID, intQuestionId, intAnswer, strData, strRemarks, strDocDate, dtLogged, strLogged)
From tblSRPEventData where intPersonnelID = @SSN and intSRPAttendId in (Select intSRPAttendId from tblSRPAttendance where intEventId = @EventId)
--Insert all records that have answer of 1 into tblPermTask
--Insert only one unique record the id's in both fields that should match are intQustionId and strssn
Insert tblPermTask (intQuestionId, intSrpAttendID, strTaskName, dtFound, strSSN, strNotes)
From tblSRPEventData as ed INNER JOIN
tblSRPQuestion as q on q.intQuestionId = ed.intQuestionId
Where intAnswer = 1 and intPersonnelID = @SSN and intSRPAttendId in (Select intSRPAttendId from tblSRPAttendance where intEventId = @EventId)