set nocount on if exists(select 1 from sysobjects where id=object_id('T1') and xtype='U') drop table T1 go create table T1 ( Idx int, PartID int, PropertyID int, PropertyValue varchar(20) ) insert into T1 select 1,1,1,'200' union all select 2,1,3,'400' union all select 3,1,5,'Hello World' union all select 4,2,1,'200' union all select 5,2,3,'60'if exists(select 1 from sysobjects where id=object_id('sp_T1') and xtype='P') drop procedure sp_T1 go create procedure sp_T1 (@PropertyID varchar(100),@Compare varchar(100),@PropertyValue varchar(100)) as declare @t table ( ID int identity(1,1), PropertyID int, Compare varchar(20), PropertyValue varchar(20) ) set @PropertyID=@PropertyID+',' set @Compare=@Compare+',' set @PropertyValue=@PropertyValue+',' while len(@PropertyID)!=0 begin insert into @t select (left(@PropertyID,charindex(',',@PropertyID)-1)),(left(@Compare,charindex(',',@Compare)-1)),(left(@PropertyValue,charindex(',',@PropertyValue)-1)) set @PropertyID=substring(@PropertyID,charindex(',',@PropertyID)+1,len(@PropertyID)) set @Compare=substring(@Compare,charindex(',',@Compare)+1,len(@Compare)) set @PropertyValue=substring(@PropertyValue,charindex(',',@PropertyValue)+1,len(@PropertyValue)) end declare @sql varchar(8000),@i int select @sql='',@i=max(id) from @t while @i>0 begin select @sql=' or (PropertyID='+rtrim(PropertyID)+' and PropertyValue '+ case Compare when '=' then '!=' when '>' then '<=' when '<' then '>=' when '>=' then '<' when '<=' then '>' when 'like' then 'not like' end +' '+PropertyValue+')'+@sql from @t where @i=ID set @i=@i-1 end select @sql='Select PartID from T1 where PartID not in(select PartID from T1 where '+right(@sql,len(@sql)-4)+') group by PartID' -- select * from @t -- print @sql exec (@sql) go select * from T1exec sp_T1 '1,3,5','=,>=,like','200,400,''%Hello%'''exec sp_T1 '1,3','=,>=','200,400'set nocount on/* Idx PartID PropertyID PropertyValue ----------- ----------- ----------- -------------------- 1 1 1 200 2 1 3 400 3 1 5 Hello World 4 2 1 200 5 2 3 60PartID ----------- 1PartID ----------- 1*/--说明部分 --1.SP的参数为3个部分,依次为PropertyID,与PropertyValue的匹配关系,PropertyValue --2.参数要同等数量(即3个参数均为3个组成部分或者2个组成部分等),并按同序排列 --3.为了查询方便,在SP中对第二参数进行了转义 --4.待补充
if exists(select 1 from sysobjects where id=object_id('T1') and xtype='U') drop table T1
go
create table T1
(
Idx int,
PartID int,
PropertyID int,
PropertyValue varchar(20)
)
insert into T1
select 1,1,1,'200' union all
select 2,1,3,'400' union all
select 3,1,5,'Hello World' union all
select 4,2,1,'200' union all
select 5,2,3,'60'if exists(select 1 from sysobjects where id=object_id('sp_T1') and xtype='P') drop procedure sp_T1
go
create procedure sp_T1 (@PropertyID varchar(100),@Compare varchar(100),@PropertyValue varchar(100))
as
declare @t table
(
ID int identity(1,1),
PropertyID int,
Compare varchar(20),
PropertyValue varchar(20)
)
set @PropertyID=@PropertyID+','
set @Compare=@Compare+','
set @PropertyValue=@PropertyValue+','
while len(@PropertyID)!=0
begin
insert into @t
select (left(@PropertyID,charindex(',',@PropertyID)-1)),(left(@Compare,charindex(',',@Compare)-1)),(left(@PropertyValue,charindex(',',@PropertyValue)-1))
set @PropertyID=substring(@PropertyID,charindex(',',@PropertyID)+1,len(@PropertyID))
set @Compare=substring(@Compare,charindex(',',@Compare)+1,len(@Compare))
set @PropertyValue=substring(@PropertyValue,charindex(',',@PropertyValue)+1,len(@PropertyValue))
end
declare @sql varchar(8000),@i int
select @sql='',@i=max(id) from @t
while @i>0
begin
select @sql=' or (PropertyID='+rtrim(PropertyID)+' and PropertyValue '+
case Compare
when '=' then '!='
when '>' then '<='
when '<' then '>='
when '>=' then '<'
when '<=' then '>'
when 'like' then 'not like'
end
+' '+PropertyValue+')'+@sql from @t where @i=ID
set @i=@i-1
end
select @sql='Select PartID from T1 where PartID not in(select PartID from T1 where '+right(@sql,len(@sql)-4)+') group by PartID'
-- select * from @t
-- print @sql
exec (@sql)
go
select * from T1exec sp_T1 '1,3,5','=,>=,like','200,400,''%Hello%'''exec sp_T1 '1,3','=,>=','200,400'set nocount on/*
Idx PartID PropertyID PropertyValue
----------- ----------- ----------- --------------------
1 1 1 200
2 1 3 400
3 1 5 Hello World
4 2 1 200
5 2 3 60PartID
-----------
1PartID
-----------
1*/--说明部分
--1.SP的参数为3个部分,依次为PropertyID,与PropertyValue的匹配关系,PropertyValue
--2.参数要同等数量(即3个参数均为3个组成部分或者2个组成部分等),并按同序排列
--3.为了查询方便,在SP中对第二参数进行了转义
--4.待补充