我想判断输入的数据 @grade和@date 是否在表中存在，不存在新增，存在就跳出循环可我写的实现不了，哪位帮帮我呀，就是下面游标那的判断！
create procedure proc_HistoryGradeStandard
/*参数*/
(
@total varchar(10) , --要处理的列
@searchTable varchar(50), --要查询的表
@uptotal varchar(10), --要更新的列
@UPTABLE varchar(50), --要更新的表
@grade varchar(50), --年级
@date varchar(50), --考试日期
@MONTH varchar(50) --考试月份
)
as
DECLARE @totalAvg float
DECLARE @SQL NVARCHAR(1000)
declare @ID int
declare @TGRADE NVARCHAR(50)
DECLARE @TDATE DATE
--声明一条sql语句
declare @sqlTotal nvarchar(1000)
set @sqlTotal='select @totalAvg=SUM('+@total+')/NULLIF(COUNT(1),0)
from '+@searchTable+'
where testdate=@date and grade=@grade'
EXEC SP_EXECUTESQL @sqlTotal,N'@totalAvg float OUTPUT,@date datetime,@grade varchar(50)',@totalAvg OUTPUT,@date,@grade
DECLARE @SIGN nvarchar(10)
/*根据考试月份判断是期中还是期末成绩*/
IF( @MONTH='10')
BEGIN
set @SIGN=1
END
IF( @MONTH='11')
BEGIN
set @SIGN=1
END
IF(@MONTH= '12')
BEGIN
set @SIGN=2
END
IF(@MONTH= '1')
BEGIN
set @SIGN=2
END
IF( @MONTH='4')
BEGIN
set @SIGN=3
END
IF( @MONTH='5')
BEGIN
set @SIGN=3
END
IF( @MONTH='6')
BEGIN
set @SIGN=4
END
IF( @MONTH='7')
BEGIN
set @SIGN=4
END
CREATE TABLE #TEMP_ALLLIST (ID INT identity,GRADE NVARCHAR(50),TESTDATE DATE)
SET @SQL=N'INSERT INTO #TEMP_ALLLIST(GRADE,TESTDATE)
SELECT GRADE,TESTDATE FROM '+@UPTABLE+''
EXEC SP_EXECUTESQL @SQL
DECLARE ID_CURSOR CURSOR SCROLL
FOR
(SELECT * FROM #TEMP_ALLLIST)
Open ID_CURSOR
--游标赋给变量
FETCH NEXT FROM ID_CURSOR INTO @ID,@TGRADE,@TDATE
--如果游标存在
WHILE(@@FETCH_STATUS=0)
BEGIN
IF(SELECT GRADE FROM #TEMP_ALLLIST WHERE ID=@ID) = @grade AND (SELECT TESTDATE FROM #TEMP_ALLLIST WHERE ID=@ID) = @date
BREAK
ELSE IF(SELECT GRADE FROM #TEMP_ALLLIST WHERE ID=@ID) <> @grade OR (SELECT TESTDATE FROM #TEMP_ALLLIST WHERE ID=@ID) <> @date
BEGIN
declare @INSERTSCORE Nvarchar(1000)
set @INSERTSCORE=N' INSERT INTO '+@UPTABLE+' (Grade, TestDate, Signs, '+@uptotal+')
values('+@grade+',CONVERT(date,@date),'+@SIGN+',@totalAvg)'
EXEC SP_EXECUTESQL @INSERTSCORE,N'@date varchar(50),@totalAvg float',@date,@totalAvg
END
FETCH NEXT FROM ID_CURSOR INTO @ID,@TGRADE,@TDATE --跳到下个游标
END
CLOSE ID_CURSOR
DEALLOCATE ID_CURSOR --释放游标
DROP TABLE #TEMP_ALLLIST
不懂啊！
