CREATE PROCEDURE TJ @ViewName varchar(50) AS BEGIN declare @sql varchar(8000) set @sql = 'select xh,xm' select @sql = @sql + ',sum(case SFXM when '''+SFXM +''' then money end) ['+SFXM +']' from (select distinct SFXM from ChargeItem where SFXM is not null ) as a
select @sql = @sql+' from '+@ViewName+' group by xh,xm order by xh'
exec(@sql) END GOEXEC TJ '视图名称'
CREATE PROCEDURE TJ @ViewName varchar(50)AS declare @sql varchar(8000) set @sql = 'select xh,xm' select @sql = @sql + ',['+SFXM+']=sum(case SFXM when '''+SFXM +''' then money end) ' from (select distinct SFXM from ChargeItem where SFXM is not null ) as a select @sql = @sql+' from '+@ViewName+' group by xh,xm order by xh' exec(@sql)
to churchatp1(别看资料,看聊效!) 只传varchar不行to libin_ftsafe(子陌红尘) 、wgsasd311(自强不息) 我要传入的变量不是@ViewName 是另外一个参数, 参数名称我忘记写了, 叫什么无所谓。这个参数是放在(select distinct SFXM from ChargeItem where SFXM is not null ) 这个语句中的。 比如参数是(SFXM='学费' or SFXM='杂费')那么我要统计出的收费情况只是学费和杂费的。
是不是前面有什么字段,Where就要加上一个 字段 is not null条件
Where SFXM in ('学费','杂费')
to zxbyhcsdn(沙子) 之所以加上 where SFXM is not null 就是为了以后增加新的变量,先放上一个不可能出现的条件。 我也是想向你的那样where SFXM is not null and SFXM in ('学费','杂费') 这样可以实现。 但直接写在语句里可以。 可通过变参传如何实现, 这才是我要问的问题。
zxbyhcsdn(沙子) 我试过把'学费','杂费' 作为一个字符串变量传到存储过程中去,定义一个@SFXMList变量,但SQL语句不支持where SFXM is not null and SFXM in (@SFXMList) 这样语句不能通过
select @sql = @sql + ',sum(case SFXM when '''+ChargeItemName+''' then money end) ['+ChargeItemName+']' from (select distinct ChargeItemName from ChargeItem where charindex(','+ChargeItemName+',',','+@ItemList+',')>0) as a select @sql = @sql+' from '+@ViewName+' group by xh,xm order by xh' charindex应该好使
@ViewName varchar(50)
AS
BEGIN
declare @sql varchar(8000)
set @sql = 'select xh,xm'
select
@sql = @sql + ',sum(case SFXM when '''+SFXM +''' then money end) ['+SFXM +']'
from
(select distinct SFXM from ChargeItem where SFXM is not null ) as a
select @sql = @sql+' from '+@ViewName+' group by xh,xm order by xh'
exec(@sql)
END
GOEXEC TJ '视图名称'
declare @sql varchar(8000)
set @sql = 'select xh,xm'
select @sql = @sql + ',['+SFXM+']=sum(case SFXM when '''+SFXM +''' then money end) ' from (select distinct SFXM from ChargeItem where SFXM is not null ) as a
select @sql = @sql+' from '+@ViewName+' group by xh,xm order by xh'
exec(@sql)
from (select distinct ChargeItemName from ChargeItem where charindex(','+ChargeItemName+',',','+@ItemList+',')>0) as a
select @sql = @sql+' from '+@ViewName+' group by xh,xm order by xh'
charindex应该好使