···存储过程的过滤条件如何实现多项选择?···
以下是我写的一个存储过程,比较粗糙。但是,我现在想实现@cInvCode 这个参数能在uap报表的过滤条件中实现多项选择,就是一次可以选择多个存货档案进行报表查询,不知要如何写,请高人指教,小弟在此先拜谢了!!!alter procedure tabAccountNew
@Sdate varchar(10)='',
@Edate varchar(10)='',
@cInvCode varchar(50)='',
@cwhcode varchar(100)='',
@cfree varchar(100)=''
as
declare @sql nvarchar(4000),@filterStr varchar(100),@cwh varchar(100),@cf varchar(100)
if @cInvCode <> ''
set @filterStr = 'a.cInvCodelike''%' + @cInvCode + '%'''
else set @filterStr='1 = 1'
if @Sdate = ''
set @Sdate= '1 = 1'
if @Edate= ''
set @Edate= '1 = 1'
if @cwhcode <> ''
set @cwh = 'b.cwhcode like ''%' + @cwhcode + '%'''
else set @cwhcode ='1 = 1'
if @cfree <>''
set @cf = 'a.cFree1 like ''%' + @cfree + '%'''
else set @cf = '1 = 1'
SET @sql = '
delete from dbo.a
INSERT INTO dbo.a select cInvCName,a.cwhcode,a.cInvCode,cInvName,a.cFree1,sum(在途量)ZTL,sum(成品入库量) RKL,sum(销售出库量) CKL,iQuantity from(
--调拨在途
select cInvCName,a.cwhcode,b.cInvCode,cInvName,cFree1,b.iQuantity as 在途量,0 成品入库量,0 as 销售出库量 from RdRecord a, RdRecords b ,Inventory c ,InventoryClass d where a.id=b.id and a.cbustype= ''调拨入库'' and csource=''调拨''
and isnull(a.chandler,'''')='''' and convert(varchar(10),ddate,120) between ''' + @Sdate + ''' and ''' + @Edate + ''' and c.cInvCode=b.cInvCode and c.cInvCCode = d.cInvCCode
union
--产品入库
select cInvCName,a.cwhcode,b.cInvCode,cInvName,cFree1,0 在途量,b.iQuantity as 成品入库量,0 as 销售出库量 from RdRecord a,RdRecords b, Inventory c ,InventoryClass d where a.id=b.id and a.cbustype=''成品入库''
and isnull(a.chandler,'''')<>'''' and convert(varchar(10),ddate,120) between ''' + @Sdate + ''' and ''' + @Edate + ''' and c.cInvCode=b.cInvCode and c.cInvCCode = d.cInvCCode
union
--销售出库量
select cInvCName,a.cwhcode,b.cInvCode,cInvName,cFree1,0 在途量,0 成品入库量,b.iQuantity as 销售出库量 from RdRecord a,RdRecords b, Inventory c ,InventoryClass dwhere a.id=b.id and a.cbustype=''普通销售''
and isnull(a.chandler,'''')<>'''' and convert(varchar(10),ddate,120) between ''' + @Sdate + ''' and ''' + @Edate + ''' and c.cInvCode=b.cInvCode and c.cInvCCode = d.cInvCCode
) a ,CurrentStock b where ' + @filterStr +' and ' + @cwh + ' and ' + @cf + ' andb.cInvCode=a.cInvCode and b.cwhcode=a.cwhcode
group by cInvCName,a.cwhCode,a.cInvCode,cInvName,a.cFree1,iQuantity
select * from dbo.a'
EXEC (@sql) 好贵啊,先收藏,有空再说了 附件太贵了,50个金币啊 :L太贵了吧~这要怎么下 这个问题到了一定深度了 上面附件是如何使存储过程的参数与UAP报表中过滤参数(区间值)对应起来 是个文本文件,里面有2个例子 都这么贵啊,穷啊 什么东西这么贵啊,买不起 这个有点贵哟 研究下... :lol:lol太贵了........................... 我看了一下,写的不错..............:lol:lol:lol 顶,楼主啊!naojin的太贵了哦! 能不能不要把金币设那么高啊。 筹了3天金币,2楼的文本文件方法不能解决楼主的判定存储过程参数值,并更改最终执行的sql语句得出不同结果的要求啊!:Q:'( 什么东西,我也遇到这个问题,有人能解决一下吗
页:
[1]
2