帮我看以下存储过程,这个存储过程在‘检查语法’时提示‘检查语法成功’。但之后我在查询分析器里输入 EXEC K_AccountUnitSumScore 时出现:
服务器: 消息 170,级别 15,状态 1,行 10
第 10 行: ',' 附近有语法错误。我不知道为什么,请大家帮我看看。谢谢了。
CREATE Proc K_AccountUnitSumScoreas
set nocount on
declare @years varchar(10) --当前年限
declare @sql varchar(5000) --sql语句
set @years=year(getdate())
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Kh_UnitSumScore'+@years+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
set @sql='
CREATE TABLE [dbo].[Kh_UnitSumScore'+@years+'] (
[Code] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Name] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[PerCount] [int] null,
[SumPeriod] [float] NULL ,
[SumScore] [float] NULL ,
[AvgPeriod] [float] NULL ,
[AvgScore] [float] NULL
)'
exec(@sql)
end
set @sql='delete from Kh_UnitSumScore'+@years
exec(@sql)
set @sql='insert into Kh_UnitSumScore'+@years+'(Code,Name,PerCount)select
GroupID,
Name,
PerCount=(select count(U.UserID) from FS_User_Group U inner join Fs_Members M on U.UserID=M.ID where M.ZwRate>2 and U.GroupID=info.GroupID )
from
FS_MemGroup info
where
IsPm=1, ' --只显示允许排名组
exec(@sql)
--将所有人的分数求合记算>24学时的按24学时算
set @sql='insert into kh_temp(UserID,GetPeriod,GetScore)
select
UserID,
GetPeriod=case when sum(isnull(GetPeriod,0))>24 then 24 else sum(isnull(GetPeriod,0)) end,
GetScore=sum(isnull(GetScore,0))
from
Kh_User_Info
where
year(StudyTime)='+@years+'group by UserID'
exec(@sql)
--更新总学时、学分
set @sql='
Update
Kh_UnitSumScore'+@years+'
Set
SumPeriod=(Select isnull(sum(Isnull(GetPeriod,0)),0) from kh_temp
where
UserID in (Select UserID from FS_User_Group U inner join FS_Members M on M.ID=U.UserID where U.GroupID=Kh_UnitSumScore'+@years+'.Code and M.ZwRate>1)),
SumScore=(Select sum(Isnull(GetScore,0)) from kh_temp
where UserID in (Select UserID from FS_User_Group U inner join FS_Members M on M.ID=U.UserID where U.GroupID=Kh_UnitSumScore'+@years+'.Code and M.ZwRate>1))'
exec(@sql)
--更新平均学时学分
set @sql='
Update
Kh_UnitSumScore'+@years+'
Set
SumPeriod=isnull(SumPeriod,0),
SumScore=isnull(SumScore,0),
AvgPeriod=isnull(SumPeriod,0)/PerCount,
AvgScore=isnull(SumScore,0)/PerCount
where PerCount<>0'
exec(@sql)
--删除临时表
delete kh_temp
set nocount on
GO
服务器: 消息 170,级别 15,状态 1,行 10
第 10 行: ',' 附近有语法错误。我不知道为什么,请大家帮我看看。谢谢了。
CREATE Proc K_AccountUnitSumScoreas
set nocount on
declare @years varchar(10) --当前年限
declare @sql varchar(5000) --sql语句
set @years=year(getdate())
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Kh_UnitSumScore'+@years+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
set @sql='
CREATE TABLE [dbo].[Kh_UnitSumScore'+@years+'] (
[Code] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Name] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[PerCount] [int] null,
[SumPeriod] [float] NULL ,
[SumScore] [float] NULL ,
[AvgPeriod] [float] NULL ,
[AvgScore] [float] NULL
)'
exec(@sql)
end
set @sql='delete from Kh_UnitSumScore'+@years
exec(@sql)
set @sql='insert into Kh_UnitSumScore'+@years+'(Code,Name,PerCount)select
GroupID,
Name,
PerCount=(select count(U.UserID) from FS_User_Group U inner join Fs_Members M on U.UserID=M.ID where M.ZwRate>2 and U.GroupID=info.GroupID )
from
FS_MemGroup info
where
IsPm=1, ' --只显示允许排名组
exec(@sql)
--将所有人的分数求合记算>24学时的按24学时算
set @sql='insert into kh_temp(UserID,GetPeriod,GetScore)
select
UserID,
GetPeriod=case when sum(isnull(GetPeriod,0))>24 then 24 else sum(isnull(GetPeriod,0)) end,
GetScore=sum(isnull(GetScore,0))
from
Kh_User_Info
where
year(StudyTime)='+@years+'group by UserID'
exec(@sql)
--更新总学时、学分
set @sql='
Update
Kh_UnitSumScore'+@years+'
Set
SumPeriod=(Select isnull(sum(Isnull(GetPeriod,0)),0) from kh_temp
where
UserID in (Select UserID from FS_User_Group U inner join FS_Members M on M.ID=U.UserID where U.GroupID=Kh_UnitSumScore'+@years+'.Code and M.ZwRate>1)),
SumScore=(Select sum(Isnull(GetScore,0)) from kh_temp
where UserID in (Select UserID from FS_User_Group U inner join FS_Members M on M.ID=U.UserID where U.GroupID=Kh_UnitSumScore'+@years+'.Code and M.ZwRate>1))'
exec(@sql)
--更新平均学时学分
set @sql='
Update
Kh_UnitSumScore'+@years+'
Set
SumPeriod=isnull(SumPeriod,0),
SumScore=isnull(SumScore,0),
AvgPeriod=isnull(SumPeriod,0)/PerCount,
AvgScore=isnull(SumScore,0)/PerCount
where PerCount<>0'
exec(@sql)
--删除临时表
delete kh_temp
set nocount on
GO
服务器: 消息 170,级别 15,状态 1,行 10
第 10 行: ',' 附近有语法错误。 这个一般在什么情况下会出现的。
IsPm=1, ' --只显示允许排名组 多了个豆号
exec(@sql)
多引号了