sql2005 reporting service报错
数据库里执行存储过过程没问题。可是在reporting service中执行这个存储过程提示
查询中存在错误。不允许从数据类型 sql_variant 到 int 的隐式转换。请使用 CONVERT 函数来运行此查询。
存储过程
ALTER PROCEDURE [dbo].[P_RPT_FlashReport_ByChannel_gx]
@Year integer,
@Month integer,
@ProductID integer,--产品id
@TerritoryID integer,--区域,传0即可代表全国
@ChannelType integer,--渠道,(1:ds药房2:mt商超3:hp医院0:total其他)
@ChannelLevel integer--级别(1:A 2:B 3:C 4:D 5:OTHERS 0:TOTAL)
as
begin
set nocount on--关闭影响行数
set ansi_warnings off--关闭聚合函数警告
--设置变量
declare @strSQL nvarchar(4000)
declare @i integer
declare @strLevel nvarchar(200)
declare @strType nvarchar(200)
declare @ProductPath nvarchar(100)
declare @TerritoryPath nvarchar(100)
declare @beginQTDMonth integer
--给变量销售路径,渠道路径赋值(product_id=92的path 和sales_path是不同的)
select @ProductPath=Sales_Path from TblBProduct where Product_ID=@ProductID
select @TerritoryPath=Path from TblBTerritory where Territory_ID=@TerritoryID--传入的值是0
--给变量渠道赋值
select @strType=case @ChannelType when 1 then ' =1'
when 2 then ' =2'
when 3 then ' =3'
when 5 then ' =5'
when 0 then ' in (1,2,3,5)'
end
--给变量级别赋值
select @strLevel=case @ChannelLevel when 1 then ' =1'
when 2 then ' =2'
when 3 then ' =3'
when 4 then ' =4'
when 5 then ' =5'
when 0 then ' in (1,2,3,4,6)'
end
--创建临时表(显示所有区域)
create table #T(Region nvarchar(50),Office nvarchar(50),City nvarchar(50),Province nvarchar(50))
begin
insert into #T
SELECT distinct b.Territory_Name as Region,--区域(表头)
c.Territory_Name as Office,--办事处
a.Territory_Name as City,
a.Province
FROM TblBTerritory a
inner join TblBTerritory b
on left(a.Path,11)=b.Path--(显示大区)
inner join TblBTerritory c
on left(a.Path,17)=c.Path --(办事处)
where a.Tree_Level=3 AND a.IS_DELETE=0--tree_level(0:全国1:大区2:办事处3:城市)
end
select * from #T
set nocount off
end