SQL,不定条件查询,case,when,then,end,isnull,join,inner,on
2008-07-11 11:07 Virus-BeautyCode 阅读(1557) 评论(0) 编辑 收藏 举报create procedure SelectProduceInfo
@Ope varchar(50),
@ProBeginDate datetime,
@ProEndDate datetime,
@IP varchar(50),
@ComputerName varchar(50),
@ProType varchar(50),
@DiskType varchar(50),
@KBID varchar(50),
@CustName varchar(50),
@CfgID int,
@ProBatch varchar(50)
as
select Operator as '操作员',ProduceDate as '生产日期',IPAddress as 'IP地址',ComputerName as '计算机名',
'产品类型'=case ProductType when '1' then 'Key' when '2' then 'U盘' when '3' then 'U盘/Key' end,
'U盘类型'=case UdiskType when '0' then '单机版安全U盘' when '1' then '企业版安全U盘' when '2' then '企业版管理盘' end,
KBUdiskID as 'U盘金邦ID',CustomerName as '客户',CfgName as '配置',
ProduceBatch as '生产批次号',ProduceRemark as '生产备注' from ProduceUdiskKeyInfo a inner join UdiskPolicyConfigInfo b
on a.ConfigAutoID=b.ConfigAutoID
where Operator=isnull(@Ope,Operator) and ProduceDate>=isnull(@ProBeginDate,ProduceDate)
and ProduceDate<=isnull(@ProEndDate,ProduceDate) and IPAddress=isnull(@IP,IPAddress)
and ComputerName=isnull(@ComputerName,ComputerName) and ProductType=isnull(@ProType,ProductType)
and UdiskType=isnull(@DiskType,UdiskType) and KBUdiskID=isnull(@KBID,KBUdiskID)
and CustomerName=isnull(@CustName,CustomerName) and a.ConfigAutoID=isnull(@CfgID,a.ConfigAutoID)
and ProduceBatch=isnull(@ProBatch,ProduceBatch)
go
@Ope varchar(50),
@ProBeginDate datetime,
@ProEndDate datetime,
@IP varchar(50),
@ComputerName varchar(50),
@ProType varchar(50),
@DiskType varchar(50),
@KBID varchar(50),
@CustName varchar(50),
@CfgID int,
@ProBatch varchar(50)
as
select Operator as '操作员',ProduceDate as '生产日期',IPAddress as 'IP地址',ComputerName as '计算机名',
'产品类型'=case ProductType when '1' then 'Key' when '2' then 'U盘' when '3' then 'U盘/Key' end,
'U盘类型'=case UdiskType when '0' then '单机版安全U盘' when '1' then '企业版安全U盘' when '2' then '企业版管理盘' end,
KBUdiskID as 'U盘金邦ID',CustomerName as '客户',CfgName as '配置',
ProduceBatch as '生产批次号',ProduceRemark as '生产备注' from ProduceUdiskKeyInfo a inner join UdiskPolicyConfigInfo b
on a.ConfigAutoID=b.ConfigAutoID
where Operator=isnull(@Ope,Operator) and ProduceDate>=isnull(@ProBeginDate,ProduceDate)
and ProduceDate<=isnull(@ProEndDate,ProduceDate) and IPAddress=isnull(@IP,IPAddress)
and ComputerName=isnull(@ComputerName,ComputerName) and ProductType=isnull(@ProType,ProductType)
and UdiskType=isnull(@DiskType,UdiskType) and KBUdiskID=isnull(@KBID,KBUdiskID)
and CustomerName=isnull(@CustName,CustomerName) and a.ConfigAutoID=isnull(@CfgID,a.ConfigAutoID)
and ProduceBatch=isnull(@ProBatch,ProduceBatch)
go