[프로시저] 배열 사용

CREATE PROCEDURE pro_ArrayLec @Sdate varchar(10), @Edate varchar(10) ,@SetDATA VARCHAR(8) ,@Deptmajorcode varchar(4) ,@RegSemester varchar(2) ,@RegYear varchar(4)
as

declare @week int

set @week=-1

if @SetDATA=''
begin
set @SetDATA='30001801'
end

WHILE (@week) < 15
begin
set @week =@week +1
set @Sdate =DateAdd(ww,@week,''+@Edate+'')

-- 강의등록일 업데이트
update  수업운영관리 set LectureReg = isnull(replace(CONVERT(varchar(50), b.regdate, 120) ,'-','/'),0)
--select LectureReg = isnull(b.regdate,0)
from  수업운영관리 a,
            (select top 1 subjectcode, regdate  from  SubjectAnnounce where subjectcode =''+@SetDATA+''
             and (RegDate >=''+DateAdd(ww,@week,''+@Edate+'') +'' and  RegDate <=  ''+ DateAdd( dd, 6, @Sdate)+'') order by RegDate ) b
             where a.regyear=@RegYear and a.regsemester=@Regsemester and a.Deptmajorcode=@Deptmajorcode  and a.subjectcode=@SetDATA  and a.weekterm=''+(@week+1)+''

-- 과목공지 업데이트
update  수업운영관리 set NoticeCnt = isnull(cnt,0)
from  수업운영관리 a,
 (select distinct subjectcode , count([NO]) as cnt   from  SubjectAnnounce where subjectcode =''+@SetDATA+''
 and (RegDate >=''+DateAdd(ww,@week,''+@Edate+'') +'' and  RegDate <=  ''+ DateAdd( dd, 6, @Sdate)+'') group by subjectcode ) b
             where a.regyear=@RegYear and a.regsemester=@Regsemester and a.Deptmajorcode=@Deptmajorcode  and a.subjectcode=@SetDATA  and a.weekterm=''+(@week+1)+''

IF (@week) =14
BREAK
ELSE
CONTINUE
end

GO

[출처] Mssql 배열 사용 1|작성자 강묘정

 

CREATE  PROCEDURE up_수업운영관리DeptArray    @RegYear varchar(4) ,@regsemester varchar(2) ,@Sdate varchar(10) , @Chdate varchar(10) , @DATA VARCHAR(1000)
AS

--declare @DATA VARCHAR(1000)
declare @SelData varchar(8) --학부코드
--@DATA 학과 코드 --> 추후 자동으로 받게 변경 당분간 수동입력 -- 추후 (수정예정)
--set @DATA = '0302,0402,0705,0706,0707,0708,0709,0804,0805,0806,0903,0904,1002,9900,'
BEGIN

WHILE CHARINDEX(',',@DATA)<>0
  BEGIN
      
   set @SelData = SUBSTRING(@DATA,1,CHARINDEX(',',@DATA)-1) --CHARINDEX ->split으로 생각함
   SET @DATA=SUBSTRING(@DATA,CHARINDEX(',',@DATA)+1,LEN(@DATA))               
 
       exec  up_수업운영관리Base @RegYear , @regsemester , @SelData ,@Sdate ,@Chdate
       --select  @SelData
        IF CHARINDEX(',',@DATA)=0 -- @DATA 배열값이 0이되면 while구문빠져나온당
 break
       else
 continue

  END
 
END

[출처] Mssql 배열 사용 2|작성자 강묘정