问过好多类似的问题,不过这次是综合性的。
我把需要转换得数据插入到临时表里面了。之后进行交叉查询,报错:@T 没有声明。
CREATE PROCEDURE pro_sample
@A varchar(8000),
@B int,
@C datetime
AS
declare @T table( A varchar(8000),
B int,
C datetime
)
insert @T select *
from table
where 1=1
and (@A is null or a like @A+'%')
and (@B is null or b = @B)
and (@C is null or c = @C)
order by a,b,c
set @sql='select A ,sum(B) 合计'
select @sql=@sql+',sum(case when convert(char(10),C,120)='''+
convert(char(10),C,120)+''' then B end) ['+convert(char(10),C,120)+']'from table group by C
set @sql=@sql+' from @T group by A'
exec(@sql)
是不是语法写错了?
我把需要转换得数据插入到临时表里面了。之后进行交叉查询,报错:@T 没有声明。
CREATE PROCEDURE pro_sample
@A varchar(8000),
@B int,
@C datetime
AS
declare @T table( A varchar(8000),
B int,
C datetime
)
insert @T select *
from table
where 1=1
and (@A is null or a like @A+'%')
and (@B is null or b = @B)
and (@C is null or c = @C)
order by a,b,c
set @sql='select A ,sum(B) 合计'
select @sql=@sql+',sum(case when convert(char(10),C,120)='''+
convert(char(10),C,120)+''' then B end) ['+convert(char(10),C,120)+']'from table group by C
set @sql=@sql+' from @T group by A'
exec(@sql)
是不是语法写错了?
@A varchar(8000),
@B int,
@C datetime
AS
create table #T( A varchar(8000),
B int,
C datetime
)
insert #T select *
from table
where 1=1
and (@A is null or a like @A+'%')
and (@B is null or b = @B)
and (@C is null or c = @C)
order by a,b,c
set @sql='select A ,sum(B) 合计'
select @sql=@sql+',sum(case when convert(char(10),C,120)='''+
convert(char(10),C,120)+''' then B end) ['+convert(char(10),C,120)+']'from #t group by C
set @sql=@sql+' from #T group by A'
exec(@sql)
那在最后拼接时这些条件还要在写一遍吧?
set @sql='select A ,sum(B) 合计'
select @sql=@sql+',sum(case when convert(char(10),C,120)='''+
convert(char(10),C,120)+''' then B end) ['+convert(char(10),C,120)+']'from table where 1=1
and (@A is null or a like @A+'%')
and (@B is null or b = @B)
and (@C is null or c = @C)
group by C
set @sql=@sql+' from table where 1=1
and (@A is null or a like @A+'%')
and (@B is null or b = @B)
and (@C is null or c = @C) group by A'
exec(@sql)
如果这样写,又要注意什么细节呢?
我上面不是把表变量改成临时表了吗,临时表是可以的