[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SP inserting double tasks into database

Posted on 2011-04-26
4
Medium Priority
?
216 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:kdeutsch
  • 2
4 Comments
 
LVL 8

Expert Comment

by:vinurajr
ID: 35473279
can you please give a relational sample data where two table can be ralated with that.
0
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 2000 total points
ID: 35473667
Please check record before insert

If Exists (Select 1 from Table where ID = value)
update
else
insert into
0
 
LVL 8

Expert Comment

by:vinurajr
ID: 35473778
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

0
 

Author Closing Comment

by:kdeutsch
ID: 35721181
thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question