- SQL code
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 --声明一条sql语句 declare @sqlTotal nvarchar(1000) set @sqlTotal='select @totalAvg=SUM([email protected]+')/NULLIF(COUNT(1),0) from [email protected]+' where [email protected] and [email protected]' EXEC SP_EXECUTESQL @sqlTotal,[email protected] 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 declare @INSERTSCORE varchar(1000) [color=#FF0000]set @INSERTSCORE=N' INSERT INTO [email protected]+' (Grade, TestDate, Signs, [email protected]+',) values([email protected]+',[email protected]+',[email protected]+','+Convert(float, @totalAvg)+')'[/color] EXEC SP_EXECUTESQL @INSERTSCORE
运行到红字时就报“从数据类型 nvarchar 转换为 float 时出错”为什么呀？[email protected]
------解决方案--------------------
- SQL code
declare @INSERTSCORE varchar(1000) set @INSERTSCORE=N' INSERT INTO [email protected]+' (Grade, TestDate, Signs, [email protected]+',) values([email protected]+',[email protected]+',[email protected]+',@totalAvg)' EXEC SP_EXECUTESQL @INSERTSCORE, [email protected] float', @totalAvg
------解决方案--------------------
INSERT INTO [email protected]+' (Grade, TestDate, Signs, [email protected]+',)
======================
多个逗号吧
------解决方案--------------------
如楼上
------解决方案--------------------
测试环境下，7楼的是通过的呀
------解决方案--------------------
原始数据没问题吧？
------解决方案--------------------
if 太多，能合并的合并
------解决方案--------------------
declare @INSERTSCORE varchar(1000)
改成
Nvarchar(1000)