SQLTeam.com | Weblogs | Forums

Stored to update and Insert

sql2012

#1

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

indent preformatted text by 4 spaces

#2

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


#3

Bottom of your code is chopped off?

please put a

[code]

tag at the start and a

[/code]

tag at the end


#4

Thankyou for your reply.
I have solved the issue.
By using the Belore 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


#5

Again: [code] tags would be appreciated