如何让设置的参数为空时,则默认选取所有下拉固定项?

z418841875 2015-08-11 02:41:58
when c.Myfield1='23' then 'N56/N61' when c.Myfield1='24' then 'B170'
when c.Myfield1='25' then 'B171' end),(case when PerField3='01' then '人资部' when PerField3='02' then '财务部' when
PerField3='03' then '资讯部'
when PerField3='04' then '客服部' when PerField3='05' then '关务部'
when PerField3='06' then '计划部' when PerField3='07' then '资材部'
when PerField3='08' then '工程技术部' when PerField3='09' then '安全课'
when PerField3='10' then '运作部' when PerField3='11' then '压铸模具'
when PerField3='12' then '品保部' end ),EmpTypeName,b.EmpNo,EmpName,IDCardNo,DtName,c.PartName,bb,
LevelLevel,OverTime1,ComeDate,LeaveDate,PayNo6,PayNo16,PayNo17,
OverTime2,OverTime3,OverTimeAll,WTType2Num,WTType3Num,
PayNo21,PayNo22,PayNo23,PayNo25,PayNo26,PayNo2,PayNo3,PayNo4,
PayNo27,PayNo42,PayNo32,PayNo33,PayNo34,PayNo35,PayNo43,
PayNo37,PayNo38,PayNo39,PayNo40,PayNo13,PayNo14,PayNo15,PayNo16
PayNo5,PayNo7,PayNo8,PayNo9,PayNo10,PayNo21+PayNo22+PayNo23+PayNo24+PayNo29 as 公司成本,
PayNo28+PayNo30 as 个人成本,
PayNo11,PayNo12,PayNo43,PayNo18,PayNo20,PayNo24,PayNo28,PayNo29,PayNo30,ComeSourceNo
InComeTax,PayNo36,b.MyField1 as 入职结算差,b.MyField2 as 离职结算差 from PayMonthData a left join AttMonthData b on
a.EmpID=b.EmpID and a.YYMM=b.YYMM left join PerEmployee c on a.EmpID=c.EmpID left join bbview on c.PartName=bbview.PartName
where a.YYMM='{YYMM}' and PerField3!='' and PerField3 is not null and ComeSourceNo='08' and PayNo20>=0
order by PerField3,PartName
else
select(case when c.Myfield1='01' then 'B24' when c.Myfield1='05' then 'X241'
when c.Myfield1='06' then '其他' when c.Myfield1='15' then '办公人员'
when c.Myfield1='17' then '铣床' when c.Myfield1='18' then 'TMA'
when c.Myfield1='19' then '其他/保洁员' when c.Myfield1='20' then 'N12'
when c.Myfield1='21' then 'X340' when c.Myfield1='22' then 'B9'
when c.Myfield1='23' then 'N56/N61' when c.Myfield1='24' then 'B170'
when c.Myfield1='25' then 'B171' end),(case when PerField3='01' then '人资部' when PerField3='02' then '财务部' when
PerField3='03' then '资讯部'
when PerField3='04' then '客服部' when PerField3='05' then '关务部'
when PerField3='06' then '计划部' when PerField3='07' then '资材部'
when PerField3='08' then '工程技术部' when PerField3='09' then '安全课'
when PerField3='10' then '运作部' when PerField3='11' then '压铸模具'
when PerField3='12' then '品保部' end ),EmpTypeName,b.EmpNo,EmpName,IDCardNo,DtName,c.PartName,bb,
LevelLevel,OverTime1,ComeDate,LeaveDate,PayNo6,PayNo16,PayNo17,
OverTime2,OverTime3,OverTimeAll,WTType2Num,WTType3Num,
PayNo21,PayNo22,PayNo23,PayNo25,PayNo26,PayNo2,PayNo3,PayNo4,
PayNo27,PayNo42,PayNo32,PayNo33,PayNo34,PayNo35,PayNo43,
PayNo37,PayNo38,PayNo39,PayNo40,PayNo13,PayNo14,PayNo15,PayNo16
PayNo5,PayNo7,PayNo8,PayNo9,PayNo10,PayNo21+PayNo22+PayNo23+PayNo24+PayNo29 as 公司成本,
PayNo28+PayNo30 as 个人成本,
PayNo11,PayNo12,PayNo43,PayNo18,PayNo20,PayNo24,PayNo28,PayNo29,PayNo30,ComeSourceNo
InComeTax,PayNo36,b.MyField1 as 入职结算差,b.MyField2 as 离职结算差 from PayMonthData a left join AttMonthData b on
a.EmpID=b.EmpID and a.YYMM=b.YYMM left join PerEmployee c on a.EmpID=c.EmpID left join bbview on c.PartName=bbview.PartName
where a.YYMM='{YYMM}' and PerField3 is not null and ComeSourceNo='08' and PayNo20>=0
order by PerField3,PartName



我想把PerField3做成参数,所有项都放进下拉固定项里去让用户选取
如果什么都不选的话就默认查询所有项。
请问该如何写呢?
...全文
53 点赞 收藏 1
写回复
1 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Tiger_Zhao 2015-08-11
将例子简化一下
DECLARE @t table(id int, PerField3 varchar(2))

INSERT INTO @t
SELECT 1,'01' UNION ALL
SELECT 2,'02' UNION ALL
SELECT 3,'03' UNION ALL
SELECT 4,'' UNION ALL
SELECT 5,NULL

DECLARE @PerField3 varchar(2) -- 参数
DECLARE @i int

SET @i = 1
WHILE @i <= 4
BEGIN
-- 模拟不同的参数
IF @i > 3
SET @PerField3 = NULL
ELSE
SET @PerField3 = '0'+CONVERT(varchar(2),@i)

SELECT *
FROM @t
WHERE PerField3!='' and PerField3 is not null
AND ( @PerField3 IS NULL
OR PerField3 = @PerField3
)

SET @i = @i + 1
END

         id PerField3
----------- ---------
1 01

         id PerField3
----------- ---------
2 02

         id PerField3
----------- ---------
3 03

         id PerField3
----------- ---------
1 01
2 02
3 03
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-08-11 02:41
社区公告
暂无公告