非动态SQL如何传入变量到IN

Question

意思大概是这样的（但是肯定会报错）：declare @str varchar(200);create table #t(i int);
insert into #t 
select 1
union all 
select 3
union all 
select 4
union all 
select 5;set @str='8,9';select * from #t
where i in (@str);drop table #t;
传入一个带有8,9,0这样的变量到非动态SQL的IN,(不要变量SQL哦,考虑编译和执行的效率)

Answer 1

declare @str1 int,@str2 int;create table #t(i int);
insert into #t 
select 1
union all 
select 3
union all 
select 4
union all 
select 5;set @str1=4
set @str2=5;select * from #t
where i=@str1 or i=@str2;drop table #t;/**
i           
----------- 
4
5（所影响的行数为 2 行）
**/

Answer 2

考虑编译和执行的效率，实在没办法才用变量SQL语句，因为非动态SQL，在存储过程里是会被编译的，而变量SQL语句不会。这条SQL、又是最频繁要执行的。

Answer 3

为什么很方便的动态SQL语句不用呢
declare @str varchar(200);create table #t(i int);
insert into #t 
select 1
union all 
select 3
union all 
select 4
union all 
select 5;set @str='1,2,3';EXEC('select * from #t where i in ('+@str+')');
/*
i
-----------
1
3(2 行受影响)*/
drop table #t;

Answer 4

declare @str varchar(200);create table #t(i int);
insert into #t 
select 1
union all 
select 3
union all 
select 4
union all 
select 5;set @str='3,8,9';DECLARE @xmlData xml
SET @xmlData = CAST('<r><i>'+REPLACE(@str,',','</i><i>')+'</i></r>' AS XML)
SELECT * FROM #t WHERE EXISTS(SELECT 1 from
(SELECT a.i.value('.','int') AS d  FROM @xmlData.nodes('//i') a(i)) t WHERE t.d = i)drop table #t;