|
sarahmfr
Posting Yak Master
214 Posts |
 Posted - 11/13/2012 : 12:30:44
|
I have a table sectioninstructor
Sectionid,
Instructorid,
Instructorrole
-----------------------------------------------------------------------
Instructorrole (1 for primary instructor, 2 for secondary instructor)
Note I want to make sure that each section has only one primary instructor
i.e. if the new instructor is assigned primary role and there is already a record in the table that has the same sectionid and instructorrole =1 on inserting the new record I should change the instructorrole to 2
I will receive (a list of sectionids), instructorid, roleid I need to merge this list with the table sectioninstructor
I tried another way but I got an error message matched can’t have two updates
ALTER PROCEDURE [dbo].[usp_SectionInstructor_InsertList]
(
@SectionList nvarchar(1000),
@RoleID int,--(1 for primary and 2 for secondary)
@InstructorID int
)
AS
begin
select T.items as SectionID,
@InstructorID as instructorID,
@RoleID as InstructorROle
from
dbo.fn_SP2_Split(@SectionList,',') T
as Source
on
Target.SectionID=source.SectionID
and
Target.InstructorID=Source.SectionID
when Matched then
update
set Target.InstructorRole=@RoleID,
Target.updatedby=@updatedby
when not matched then
insert
(SectionID,
InstructorID,
InstructorRole
)
values
(Source.SectionID,
Source.InstructorID,
@RoleID
);
---the function
--splits a list to
CREATE FUNCTION [dbo].[fn_SP2_Split]
(@String nvarchar(max),
@Delimiter nchar(1)
)
returns @temptable TABLE (items nvarchar(max))
as
begin
declare @idx int
declare @slice nvarchar(250)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
sarah |
|
Merging a result set with a table
