|
DECLARE @SQL nvarchar(4000)
Set @SQL = 'SELECT
stu.pid as userid
, stu.name
, ll.LevelID
, (SELECT Content FROM CodeMaster WHERE CodeID = ll.step) as Step
, (SELECT TOP 1 MarkingID FROM Marking WHERE StudentProgressID = sp.ProgressId) as MarkingID
, (SELECT TOP 1 CreateDate FROM Marking WHERE StudentProgressID = sp.ProgressId) as MarkingDate
, sp.UpdateDate as UpdateDate
, (SELECT Content FROM CodeMaster WHERE CodeID = ll.part2) as part2
, ei.AdminID as AdminID
FROM Student stu
INNER JOIN StudentProgress sp ON sp.StudentID = stu.pid
INNER JOIN LearningLevel ll ON sp.LearningLevelId = ll.LevelId
LEFT JOIN EditInfo ei ON sp.ProgressID = ei.ProgressID
WHERE ll.stage = (SELECT CodeID FROM CODEMASTER WHERE Content = ''BLACK'' AND topCode = 1)
AND sp.ResultID Is Not Null AND sp.type = 1 '
IF @SearchGubun = 'A' BEGIN
-- 첨삭 안된것
IF @EditYN = 'N' BEGIN
SET @SQL = @SQL + ' AND (SELECT TOP 1 MarkingID FROM Marking WHERE StudentProgressID = sp.ProgressId) IS NULL'
END
-- 첨삭된놈
IF @EditYN = 'E' BEGIN
SET @SQL = @SQL + ' AND (SELECT TOP 1 MarkingID FROM Marking WHERE StudentProgressID = sp.ProgressId) IS NOT NULL'
END
END
-- 미선택
ELSE IF @SearchGubun = 'N' BEGIN
-- 전체
IF @EditYN = 'A' BEGIN
SET @SQL = @SQL + ' AND ei.AdminID IS NULL'
END
-- 첨삭 안된것
IF @EditYN = 'N' BEGIN
SET @SQL = @SQL + ' AND ei.AdminID IS NULL'
SET @SQL = @SQL + ' AND (SELECT TOP 1 MarkingID FROM Marking WHERE StudentProgressID = sp.ProgressId) IS NULL'
END
-- 첨삭된놈
IF @EditYN = 'E' BEGIN
SET @SQL = @SQL + ' AND ei.AdminID IS NULL'
SET @SQL = @SQL + ' AND (SELECT TOP 1 MarkingID FROM Marking WHERE StudentProgressID = sp.ProgressId) IS NOT NULL'
END
END
-- 선생님명 검색
ELSE BEGIN
-- 전체
IF @EditYN = 'A' BEGIN
SET @SQL = @SQL + ' AND ei.AdminID = @SearchGubun'
END
-- 첨삭 안된것
IF @EditYN = 'N' BEGIN
SET @SQL = @SQL + ' AND (SELECT TOP 1 MarkingID FROM Marking WHERE StudentProgressID = sp.ProgressId) IS NULL'
END
-- 첨삭된놈
IF @EditYN = 'E' BEGIN
SET @SQL = @SQL + ' AND ei.AdminID = @SearchGubun'
SET @SQL = @SQL + ' AND (SELECT TOP 1 MarkingID FROM Marking WHERE StudentProgressID = sp.ProgressId) IS NOT NULL'
END
END
----------------------------------------------------------------
-- 커서를 사용
----------------------------------------------------------------
-- 결과를입력할임시테이블
Create table #temp(userid int, username nvarchar(50), LevelID nvarchar(max), Step nvarchar(max), MarkingID nvarchar(max), MarkingDate varchar(max), UpdateDate varchar(max),
part2 varchar(max), AdminID nvarchar(50))
-- 커서
DECLARE @userid int, @username nvarchar(20), @LevelID int, @Step nvarchar(20), @MarkingID int, @MarkingDate datetime, @UpdateDate datetime, @part2 varchar(50),@AdminID varchar(50)
DECLARE @CNT INT
DECLARE MYCUR CURSOR FOR
exec (@SQL)
OPEN MYCUR
FETCH NEXT FROM MYCUR INTO @userid, @username, @LevelID, @Step, @MarkingID , @MarkingDate , @UpdateDate , @part2 , @AdminID
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @CNT= ISNULL((SELECT COUNT(*) FROM #temp WHERE userid=@userid),0)
IF @CNT=0 BEGIN
INSERT INTO #temp(userid , username , LevelID, Step , MarkingID , MarkingDate, UpdateDate, part2, AdminID )
values (@userid , @username , @LevelID, @Step , isnull(@MarkingID, '0') , isnull(@MarkingDate,''), ISNULL(@UpdateDate,''), @part2, ISNULL(@AdminID,'') )
END
ELSE
BEGIN
UPDATE #temp
SET LevelID= LevelID+','+convert(varchar,@LevelID) ,
Step= Step+','+convert(varchar,@Step) ,
MarkingID= MarkingID+','+convert(varchar,isnull(@MarkingID, '0')) ,
MarkingDate= MarkingDate+','+convert(varchar,isnull(@MarkingDate,'')),
UpdateDate = UpdateDate + ',' + Convert(varchar,isnull(@Updatedate,'')),
part2 = part2 + ',' + Convert(varchar,isnull(@part2,'')),
AdminID = AdminID + ',' + Convert(varchar,isnull(@AdminID,''))
WHERE userid=@userid
END
FETCH NEXT FROM MYCUR INTO @userid, @username, @LevelID, @Step, @MarkingID , @MarkingDate , @UpdateDate , @part2 , @AdminID
END
CLOSE MYCUR
DEALLOCATE MYCUR
--결과가입력된임시테이블조회
SELECT * FROM #temp
다른게 아니라 맨위에 변수 @SQL에 조건에 해당하는 쿼리를 넣고
아래 DECLARE MYCUR CURSOR FOR 부분에 넣으려고 합니다 근데 에러가 나네요 ㅠ
혹시 exec가 안먹는거같아서 For다음에 IF문을 넣은 쿼리를 넣어봤는데 (문자열로 한것 말고) 안되더군요 ㅠㅠ
이유아시는 분이나 혹시 하는방법이있다면 댓글 부탁 드립니다.ㅠㅠㅠ |
