Stored Procedure to Update and Insert in single SP

Question

Stored Procedure to Update and Insert in single SP

arunnrj87

Mr or Mrs. 500

Points: 565
More actions
July 17, 2015 at 2:28 am

#318990

I have Table Staffsubjects
with columns and Values
Guid AcademyId StaffId ClassId SegmentId SubjectId Status
1 500 101 007 101 555 1
2 500 101 007 101 201 0
3 500 22 008 105 555 1
I need to do 3 scenarios in this table.
1.First i need to update the row if the status column is 0 to 1
2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId
3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId
I have wrote the stored procedure to do this
But the problem is If do the update
It is reflecting in the database by changing 0 to 1
But it shows error like cannot insert the duplicate
Here is the stored Procedure what i have wrote
ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]
@AcademyId uniqueidentifier,
@StaffId uniqueidentifier,
@ClassId uniqueidentifier,
@SegmentId uniqueidentifier,
@SubjectId uniqueidentifier
AS
BEGIN
DECLARE
@GUID [uniqueidentifier] = NEWID(),
@status bit,
@Cnt1 Int,
@Cnt2 Int,
@Cnt3 Int
SET NOCOUNT ON;
Select @Cnt1=Count(*) from [dbo].[StaffSubjects] where SegmentId=@SegmentId and SubjectId=@SubjectId and StaffId=@StaffId and Status=0;
Select @Cnt2=Count(*) from [dbo].[StaffSubjects] where SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId and Status=0;
Select @Cnt3=Count(*) from [dbo].[StaffSubjects] where SegmentId<>@SegmentId and SubjectId<>@SubjectId and StaffId<>@StaffId;
BEGIN
IF @Cnt1 <> 0
UPDATE [dbo].[[dbo].[StaffSubjects] Set Status=1 where SegmentId=@SegmentId and SubjectId=@SubjectId and StaffId=@StaffId and Status=0;
END
BEGIN
SET @status=1;
IF @Cnt2 <> 0
INSERT INTO [dbo].[StaffSubjects]
(
[Guid],[AcademyId],[StaffId],[ClassId],[SegmentId],[SubjectId],[Status]
)
VALUES
(@Guid,@AcademyId,@StaffId,@ClassId,@SegmentId,@SubjectId,@Status)
END
BEGIN
SET @status=1;
IF @Cnt3 = 0
INSERT INTO [dbo].[StaffSubjects]
(
[Guid],[AcademyId],[StaffId],[ClassId],[SegmentId],[SubjectId],[Status]
)
VALUES
(@Guid,@AcademyId,@StaffId,@ClassId,@SegmentId,@SubjectId,@Status
)
END
END

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

1) Your process is full of potential concurrency holes. Don't feel bad - this is a VERY common type of mistake in data processing.

2) Please provide the entire create table script for your table (including all keys and indexes) as well as scripted INSERTs for your data and your expected data after running the sproc.

3) GUIDs are typically HORRIBLE choices for keys. They are 16 bytes and carry other issues as well.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

arunnrj87 Mr or Mrs. 500 Points: 565 More actions · Answer 2

Hi Kevin

Thank you for your valuable time.

I have solve this issue by below query.

IF EXISTS (SELECT SegmentId,SubjectId,StaffId FROM [dbo].[StaffSubjects] WHERE SegmentId = @SegmentId and SubjectId=@SubjectId and StaffId=@StaffId and Status=0)

BEGIN

UPDATE [dbo].[StaffSubjects] Set Status= 1 WHERE SegmentId = @SegmentId and SubjectId=@SubjectId and StaffId=@StaffId and Status=0

END

ELSE

BEGIN

INSERT INTO [dbo].[StaffSubjects]

([Guid],[AcademyId],[StaffId],[ClassId],[SegmentId],[SubjectId])

VALUES

(@Guid,@AcademyId,@StaffId,@ClassId,@SegmentId,@SubjectId)

END

drew.allen SSC Guru Points: 76944 More actions · Answer 3

You're using not equals when you should be using NOT EXISTS. So if the @StaffID = 101, the @SegmentID = 101, and the @SubjectID = 555 then using not equals will give you a match, because 201 <> 555.

Also, you're doing unnecessary checks. There is no reason to run

Select @Cnt1=Count(*)

from [dbo].[StaffSubjects]

where SegmentId=@SegmentId

and SubjectId=@SubjectId

and StaffId=@StaffId

and Status=0;

Just run the update. If the record exists, it will update it, and if it doesn't exist, there won't be any records to update. Getting rid of the check eliminates a table/index read.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

arunnrj87 (7/17/2015)
Hi Kevin
Thank you for your valuable time.
I have solve this issue by below query.
IF EXISTS (SELECT SegmentId,SubjectId,StaffId FROM [dbo].[StaffSubjects] WHERE SegmentId = @SegmentId and SubjectId=@SubjectId and StaffId=@StaffId and Status=0)
BEGIN
UPDATE [dbo].[StaffSubjects] Set Status= 1 WHERE SegmentId = @SegmentId and SubjectId=@SubjectId and StaffId=@StaffId and Status=0
END
ELSE
BEGIN
INSERT INTO [dbo].[StaffSubjects]
([Guid],[AcademyId],[StaffId],[ClassId],[SegmentId],[SubjectId])
VALUES
(@Guid,@AcademyId,@StaffId,@ClassId,@SegmentId,@SubjectId)
END

You only THINK you solved the problem. Yes, this can do the right operation BUT it still suffers from concurrency problems because you aren't doing the locking correctly.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service