declare @str as varchar(100) set @str = '1,2,3'exec('select * from tb where tid in ('+ @str + ')')
try: select * from table1 where charindex(','+rtrim(tid)+',',','+@tid+',')>0
--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (tid int,tname varchar(3)) insert into #T select 1,'aaa' union all select 2,'bbb' union all select 3,'ccc' union all select 4,'ddd' union all select 5,'eee' union all select 6,'fff' union all select 7,'ggg' union all select 8,'hhh' union all select 9,'iii' union all select 10,'jjj'select * from #T where tid in(1,2,3) -->在你的前台将int[] a里的元素,组成一个"1,2,3"的STRING。 /* tid tname ----------- ----- 1 aaa 2 bbb 3 ccc */
declare @t table(tid int,tname varchar(8)) insert into @t values(1 ,'aaa') insert into @t values(2 ,'bbb') insert into @t values(3 ,'ccc') insert into @t values(4 ,'ddd') insert into @t values(5 ,'eee') insert into @t values(6 ,'fff') insert into @t values(7 ,'ggg') insert into @t values(8 ,'hhh') insert into @t values(9 ,'iii') insert into @t values(10,'jjj') declare @tid varchar(40) set @tid='1,4,7' select * from @t where charindex(','+rtrim(tid)+',',','+@tid+',')>0
select * from table1 where charindex(','+rtrim(tid)+',',','+@tid+',')>0
declare @t table(tid int,tname varchar(8)) insert into @t values(1 ,'aaa') insert into @t values(2 ,'bbb') insert into @t values(3 ,'ccc') insert into @t values(4 ,'ddd') insert into @t values(5 ,'eee') insert into @t values(6 ,'fff') insert into @t values(7 ,'ggg') insert into @t values(8 ,'hhh') insert into @t values(9 ,'iii') insert into @t values(10,'jjj') declare @tid varchar(40) set @tid='1,4,7' select * from @t where charindex(','+rtrim(tid)+',',','+@tid+',')>0
declare @t table(tid int,tname varchar(8)) insert into @t values(1 ,'aaa') insert into @t values(2 ,'bbb') insert into @t values(3 ,'ccc') insert into @t values(4 ,'ddd') insert into @t values(5 ,'eee') insert into @t values(6 ,'fff') insert into @t values(7 ,'ggg') insert into @t values(8 ,'hhh') insert into @t values(9 ,'iii') insert into @t values(10,'jjj') declare @tid varchar(40) set @tid='1,4,7' select * from @t where charindex(','+rtrim(tid)+',',','+@tid+',')>0是测试用的表变量,条件你1,4,7是你传的值
人家这是写测试用例呀,这样可以不?create table ta(tid int,tname varchar(8)) insert into ta values(1 ,'aaa') insert into ta values(2 ,'bbb') insert into ta values(3 ,'ccc') insert into ta values(4 ,'ddd') insert into ta values(5 ,'eee') insert into ta values(6 ,'fff') insert into ta values(7 ,'ggg') insert into ta values(8 ,'hhh') insert into ta values(9 ,'iii') insert into ta values(10,'jjj') go declare @tid varchar(40) set @tid='1,4,7'select * from ta where charindex(','+rtrim(tid)+',',','+@tid+',')>0 drop table ta
set @str = '1,2,3'exec('select * from tb where tid in ('+ @str + ')')
select * from table1 where charindex(','+rtrim(tid)+',',','+@tid+',')>0
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (tid int,tname varchar(3))
insert into #T
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'ccc' union all
select 4,'ddd' union all
select 5,'eee' union all
select 6,'fff' union all
select 7,'ggg' union all
select 8,'hhh' union all
select 9,'iii' union all
select 10,'jjj'select * from #T where tid in(1,2,3) -->在你的前台将int[] a里的元素,组成一个"1,2,3"的STRING。
/*
tid tname
----------- -----
1 aaa
2 bbb
3 ccc
*/
where tid in(1,4,6)
insert into @t values(1 ,'aaa')
insert into @t values(2 ,'bbb')
insert into @t values(3 ,'ccc')
insert into @t values(4 ,'ddd')
insert into @t values(5 ,'eee')
insert into @t values(6 ,'fff')
insert into @t values(7 ,'ggg')
insert into @t values(8 ,'hhh')
insert into @t values(9 ,'iii')
insert into @t values(10,'jjj') declare @tid varchar(40)
set @tid='1,4,7'
select * from @t where charindex(','+rtrim(tid)+',',','+@tid+',')>0
insert into @t values(1 ,'aaa')
insert into @t values(2 ,'bbb')
insert into @t values(3 ,'ccc')
insert into @t values(4 ,'ddd')
insert into @t values(5 ,'eee')
insert into @t values(6 ,'fff')
insert into @t values(7 ,'ggg')
insert into @t values(8 ,'hhh')
insert into @t values(9 ,'iii')
insert into @t values(10,'jjj') declare @tid varchar(40)
set @tid='1,4,7'
select * from @t where charindex(','+rtrim(tid)+',',','+@tid+',')>0
insert into @t values(1 ,'aaa')
insert into @t values(2 ,'bbb')
insert into @t values(3 ,'ccc')
insert into @t values(4 ,'ddd')
insert into @t values(5 ,'eee')
insert into @t values(6 ,'fff')
insert into @t values(7 ,'ggg')
insert into @t values(8 ,'hhh')
insert into @t values(9 ,'iii')
insert into @t values(10,'jjj') declare @tid varchar(40)
set @tid='1,4,7'
select * from @t where charindex(','+rtrim(tid)+',',','+@tid+',')>0是测试用的表变量,条件你1,4,7是你传的值
insert into ta values(1 ,'aaa')
insert into ta values(2 ,'bbb')
insert into ta values(3 ,'ccc')
insert into ta values(4 ,'ddd')
insert into ta values(5 ,'eee')
insert into ta values(6 ,'fff')
insert into ta values(7 ,'ggg')
insert into ta values(8 ,'hhh')
insert into ta values(9 ,'iii')
insert into ta values(10,'jjj')
go
declare @tid varchar(40)
set @tid='1,4,7'select *
from ta
where charindex(','+rtrim(tid)+',',','+@tid+',')>0
drop table ta