create proc InPutAllowanceCount
@ExpertName nvarchar(100),
@TenerCollege nvarchar(100),
@RetainPost nvarchar(100)as
declare @sql varchar(4000)
begin
set @sql = 'select ExpertName,AgreementName,TenerCollege,RetainPost,StartRetainDate,EndRetainDate '
select @sql = @sql + ',sum(case AllowanceTypeName when '''+AllowanceTypeName+''' then AllowanceTotal else 0 end) ['+AllowanceTypeName+']'
from (select distinct AllowanceTypeName from vwExpertAllowanceCount) as a
select @sql = @sql+' from vwExpertAllowanceCount where 1=1 ' if( @ExpertName<>'' or @ExpertName is not null)
@sql = @sql+' and ExpertName='''+@ExpertName+'''' if( @TenerCollege<>'' or @TenerCollege is not null )
@sql = @sql+' and TenerCollege='''+@TenerCollege+''''
if(@TenerCollege<>'' or @RetainPost is not null)
@sql = @sql+' and RetainPost='''+@RetainPost+'''
group by ExpertName,TenerCollege,StartRetainDate,EndRetainDate,AgreementName, RetainPost'
end
exec(@sql)
@ExpertName nvarchar(100),
@TenerCollege nvarchar(100),
@RetainPost nvarchar(100)as
declare @sql varchar(4000)
begin
set @sql = 'select ExpertName,AgreementName,TenerCollege,RetainPost,StartRetainDate,EndRetainDate '
select @sql = @sql + ',sum(case AllowanceTypeName when '''+AllowanceTypeName+''' then AllowanceTotal else 0 end) ['+AllowanceTypeName+']'
from (select distinct AllowanceTypeName from vwExpertAllowanceCount) as a
select @sql = @sql+' from vwExpertAllowanceCount where 1=1 ' if( @ExpertName<>'' or @ExpertName is not null)
@sql = @sql+' and ExpertName='''+@ExpertName+'''' if( @TenerCollege<>'' or @TenerCollege is not null )
@sql = @sql+' and TenerCollege='''+@TenerCollege+''''
if(@TenerCollege<>'' or @RetainPost is not null)
@sql = @sql+' and RetainPost='''+@RetainPost+'''
group by ExpertName,TenerCollege,StartRetainDate,EndRetainDate,AgreementName, RetainPost'
end
exec(@sql)
Incorrect syntax near '@sql'.
Msg 102, Level 15, State 1, Procedure InPutAllowanceCount, Line 19
Incorrect syntax near '@sql'.
Msg 102, Level 15, State 1, Procedure InPutAllowanceCount, Line 21
Incorrect syntax near '@sql'.
报这样的错
具体的报错在如下3句
@sql = @sql+' and ExpertName='''+@ExpertName+'''' @sql = @sql+' and TenerCollege='''+@TenerCollege+'''' @sql = @sql+' and RetainPost='''+@RetainPost+''' 对变量的赋值应该加上
Set 或者 Select 所以更改为
set @sql = @sql+' and ExpertName='''+@ExpertName+'''' set@sql = @sql+' and TenerCollege='''+@TenerCollege+'''' set @sql = @sql+' and RetainPost='''+@RetainPost+''' 看来我占沙发 并且是正解了
--line 16 :
set @sql = @sql+' and ExpertName='''+@ExpertName+''''
--line 19 :
set @sql = @sql+' and TenerCollege='''+@TenerCollege+''''
--line 21 :
set @sql = @sql+' and RetainPost='''+@RetainPost+'''
@ExpertName nvarchar(100),
@TenerCollege nvarchar(100),
@RetainPost nvarchar(100)
这3个变量赋空值啊