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
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
Bottom of your code is chopped off?
please put a
[code]
tag at the start and a
[/code]
tag at the end
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
Again: [code] tags would be appreciated