Possible Duplicate:
Parameterizing an SQL IN clause?可能重复：参数化SQL IN子句？
I have read-only access to a database which I'd like to do the following on:
我有一个对数据库的只读访问权限，我想对以下内容进行以下操作：
DECLARE @var AS INT_COLLECTION = (1,2,3)
SELECT name,column
FROM table
WHERE column IN @var
Of course INT_COLLECTION doesn't exist, but is there something similar available in SQL Server 2008? Or another way to do this without write access to the database?
当然INT_COLLECTION不存在，但SQL Server 2008中是否有类似的东西？或者没有对数据库的写访问权限的另一种方法？
2 个解决方案
#1
4
This is a pretty heavy-handed approach since you have to create a user-defined table type, but it does work:
这是一个非常严厉的方法，因为你必须创建一个用户定义的表类型，但它确实有效：
CREATE TYPE INT_COLLECTION AS TABLE (
Value Int
);
GO
DECLARE @var AS INT_COLLECTION;
INSERT INTO @var (Value)
VALUES (1), (2), (3);
SELECT name, col
FROM YourTable
WHERE col IN (SELECT Value FROM @var)
#2
1
You could do something like this:
你可以这样做：
DECLARE @var AS TABLE (IDS INT);
INSERT INTO @var VALUES (1),(2),(3);
SELECT name,column
FROM table
WHERE column IN ( SELECT IDS FROM @var)
#1
4
This is a pretty heavy-handed approach since you have to create a user-defined table type, but it does work:
这是一个非常严厉的方法，因为你必须创建一个用户定义的表类型，但它确实有效：
CREATE TYPE INT_COLLECTION AS TABLE (
Value Int
);
GO
DECLARE @var AS INT_COLLECTION;
INSERT INTO @var (Value)
VALUES (1), (2), (3);
SELECT name, col
FROM YourTable
WHERE col IN (SELECT Value FROM @var)
#2
1
You could do something like this:
你可以这样做：
DECLARE @var AS TABLE (IDS INT);
INSERT INTO @var VALUES (1),(2),(3);
SELECT name,column
FROM table
WHERE column IN ( SELECT IDS FROM @var)