SQL Server 过程声明一个列表 [英] SQL Server procedure declare a list

问题描述

我的 SQL 代码相当简单.我正在尝试从这样的数据库中选择一些数据:

My SQL code is fairly simple. I'm trying to select some data from a database like this:

SELECT * FROM DBTable
WHERE id IN (1,2,5,7,10)

我想知道如何在选择之前声明列表(在变量、列表、数组或其他东西中)并且在选择内部只使用变量名称，如下所示:

I want to know how to declare the list before the select (in a variable, list, array, or something) and inside the select only use the variable name, something like this:

VAR myList = "(1,2,5,7,10)"
SELECT * FROM DBTable
WHERE id IN myList

推荐答案

您可以像这样将变量声明为临时表:

You could declare a variable as a temporary table like this:

declare @myList table (Id int)

这意味着您可以使用 insert 语句为其填充值:

Which means you can use the insert statement to populate it with values:

insert into @myList values (1), (2), (5), (7), (10)

然后你的 select 语句可以使用 in 语句:

Then your select statement can use either the in statement:

select * from DBTable
where id in (select Id from @myList)

或者你可以像这样加入临时表:

Or you could join to the temporary table like this:

select *
from DBTable d
join @myList t on t.Id = d.Id

如果你经常做这样的事情，那么你可以考虑定义一个 用户定义的表类型 这样你就可以像这样声明你的变量:

And if you do something like this a lot then you could consider defining a user-defined table type so you could then declare your variable like this:

declare @myList dbo.MyTableType

这篇关于SQL Server 过程声明一个列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！