asked on

SP inserting double tasks into database

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.

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

	--Insert all the records into tblSRpEventDataHistory
	Insert tblSrpEventDataHistory (intPersonnelId, intSRpAttendID, intQuestionId, intAnswer, strData, strRemarks, strDocDate, dtLogged, strLogged)
	Select	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)
	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 and intPersonnelID = @SSN and intSRPAttendId in (Select intSRPAttendId from tblSRPAttendance where intEventId = @EventId)

Open in new window

help.txt

vinurajr

can you please give a relational sample data where two table can be ralated with that.

ASKER CERTIFIED SOLUTION

Alpesh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vinurajr

Please Find a Code Snippet: you can develop according to your need by usign exist not exist or you can update similarly.

drop table #tblEventData;

create table #tblEventData(
 DataId integer identity
,QuestionId integer
,SSN varchar(10)
,Answer integer
,Data varchar(10)
,notes varchar(200)
);

insert into #tblEventData(QuestionId,SSN,Answer,Data,notes)
select 2,'1111111111',1,'5','sdfg'
union all
select 3,'2222222222',1,'7','sdfg'
union all
select 4,'3333333333',1,'8','sdfg'
union all
select 4,'3333333333',1,'8','sdefgh'
union all
select 5,'4444444444',1,'9','sdfg'
union all
select 74,'5555555555',0,'9','fdsgsdfg'

select * from #tblEventData

drop table #tblSRPQuestion;

create table #tblSRPQuestion(
QuestionId integer identity
,ShortTask varchar(50)
);

insert into #tblSRPQuestion
select 'ID card expired'
union all
select 'Update DEERS'
union all
select 'ADOS CODE/SIDPERS'
union all
select 'ADDRESS INCORRECT'

select * from #tblSRPQuestion

drop table #tblPermTask;

create table #tblPermTask(
PermTaskID integer identity
,QuestionId integer
,SSN varchar(10)
,ShortTask varchar(50)
);



select cast(QuestionId as varchar(3))+ SSN from #tblEventData group by QuestionId,SSN having COUNT(*) >1

insert into #tblPermTask(QuestionId,SSN,ShortTask)
select distinct E.QuestionId,SSN,ShortTask
from #tblEventData E inner join #tblSRPQuestion Q
on E.QuestionId=Q.QuestionId
where exists (
select 1 from #tblEventData EI
where cast(E.QuestionId as varchar(3))+ E.SSN = cast(EI.QuestionId as varchar(3))+ EI.SSN 
group by QuestionId,SSN having COUNT(*) >1)
and not exists (select 1 from #tblPermTask T
where E.QuestionId = T.QuestionId and E.SSN = T.SSN)


insert into #tblPermTask(QuestionId,SSN,ShortTask)
select distinct E.QuestionId,SSN,ShortTask
from #tblEventData E inner join #tblSRPQuestion Q
on E.QuestionId=Q.QuestionId
where not exists (
select 1 from #tblEventData EI
where cast(E.QuestionId as varchar(3))+ E.SSN = cast(EI.QuestionId as varchar(3))+ EI.SSN 
group by QuestionId,SSN having COUNT(*) >1)
and not exists (select 1 from #tblPermTask T
where E.QuestionId = T.QuestionId and E.SSN = T.SSN)



select * from #tblPermTask

Open in new window

kdeutsch

ASKER

thanks