create proc procName
@a varchar(8000)
as
select * from sales where ','+customer_id+',' like '%,'+@a+',%'
go--调用:
exec procName '345,2342,2342,fg'
@a varchar(8000)
as
select * from sales where ','+customer_id+',' like '%,'+@a+',%'
go--调用:
exec procName '345,2342,2342,fg'
create proc procName
@a varchar(8000)
as
select * from sales where charindex(','+@a+',',','+customer_id+',')>0
go--调用:
exec procName '345,2342,2342,fg'
create proc procName
@a varchar(8000)
as
select * from sales where ','+customer_id+',' like '%,'+@a+',%'
go--调用:
exec procName '345,2342,2342,fg'
@a varchar(8000)
as
select * from sales where ','+@a+',' '%,'+customer_id+',%'
go--调用:
exec procName '345,2342,2342,fg'
/*
生成组合条件的存储过程
*/
--创建得到组合条件的存储过程
create procedure test
@fd1 varchar(100),@value1 varchar(100)
,@fd2 varchar(100),@value2 varchar(100)
,@fd3 varchar(100),@value3 varchar(100)
,@fd4 varchar(100),@value4 varchar(100)
,@tj varchar(1000) output
as
select @tj='',@tj=@tj
+case isnull(@value1,'') when '' then ''
else ' and ['+@fd1+']='''+@value1+'''' end
+case isnull(@value2,'') when '' then ''
else ' and ['+@fd2+']='''+@value2+'''' end
+case isnull(@value3,'') when '' then ''
else ' and ['+@fd3+']='''+@value3+'''' end
+case isnull(@value4,'') when '' then ''
else ' and ['+@fd4+']='''+@value4+'''' end
if @tj<>''
set @tj=' where '+right(@tj,len(@tj)-5)
go--测试
declare @tj varchar(1000)
exec test '姓名','李XX'
,'职务',''
,'性别','男'
,'学历','本科'
,@tj output
print @tjgo
drop procedure test
create table sales
(customer_id varchar(24),
sale_amt money)create procedure pro_tj @a varchar(800)
as
declare @sql varchar(200)
set @sql = 'select * from sales where customer_id in(' +@a+')'
exec(@sql)测试:
declare @ss varchar(800)
set @ss = '''a''','''b''',c''''
exec pro_tj @ss
as
declare @sql varchar(200)
if @a = ''
set @sql = 'select * from sales where customer_id '
if @a <> ''
set @sql = 'select * from sales where customer_id in(' +@a+')'
exec(@sql)测试:
declare @ss varchar(800)
set @ss = '''a''','''b''',c''''
exec pro_tj @ss