In语句条件怎么使用变量

Question

create table #tb (id int)
insert into #tb
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5

--平常的写法
select * from #tb where id in (1,2,4,5)
--如果把in(@变量)怎么弄
declare @ids varchar(50)
select @ids = '1,2,4,5'
select * from #tb where CONVERT(varchar,id)  in (@ids)--什么也查询不出
select * from #tb where id in (@ids)--语法错误

drop table #tb

求怎么使用in的时候把条件变成变量（难道只能拼字符串么）

Answer 1

Try with dynamic sql.

Answer 2

你好，

正如楼上所说，你可以使用动态SQL 语句，请参阅下面的示例：

USE AdventureWorks
GO

DECLARE @SQL NVARCHAR(4000)
DECLARE @SQLCommand NVARCHAR(4000)
DECLARE @Command NVARCHAR(4000)
SET @SQL='SELECT * FROM Person.Contact WHERE '
SET @Command='ContactID IN (1,2,3)'
SET @SQLCommand=@SQL+@Command
--PRINT @SQLCommand
EXECUTE sp_executesql @SQLCommand

更多信息请参阅：
sp_executesql (Transact-SQL)
http://msdn.microsoft.com/zh-cn/library/ms188001.aspx

谢谢，
邱爱华

---

Ai-hua Qiu[MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

谢谢楼上两位回答

我已经实现我想要的效果(非动态SQL，因为局限性太大了)