在子句中使用SQL Server变量

Here is a simple example - why does this work:

这是一个简单的例子 - 为什么这个工作：

DECLARE @v as varchar(75)
SET @v = 'xxx-xxxx'
SELECT * FROM tbl_skus WHERE SKU = @v

But this does not work:

但这不起作用：

DECLARE @v as varchar(75)
SET @v = 'xxx-xxxx,yyy-yyyy'
SELECT * FROM tbl_skus WHERE SKU IN ( @v )

Both SKUs 'xxx-xxxx' and 'yyy-yyyy'

两个SKU的'xxx-xxxx'和'yyy-yyyy'

are in the table. The first query pulls 1 result, and the second pulls 0 results; no errors.

在桌子上。第一个查询拉出1个结果，第二个查询拉0个结果;没有错误。

3 个解决方案

#1

You cannot assign two values to a single variable. You will have to do something like this:

您不能将两个值分配给单个变量。你必须做这样的事情：

DECLARE @v as varchar(75) DECLARE @a as varchar(75)

SET @v = 'xxx-xxxx' SET @a = 'yyy-yyyy'

SELECT * FROM tbl_skus
WHERE SKU IN (@v, @a);

#2

The IN doesn't support passing in a list of values that way. Try it one of these two ways:

IN不支持以这种方式传递值列表。尝试以下两种方式之一：

DECLARE @vList TABLE(v VARCHAR(75));

INSERT INTO @vList
VALUES('xxx-xxxx'),('yyy-yyyy');

SELECT * 
FROM tbl_skus 
WHERE SKU IN ( SELECT v FROM @vList)

Or you could:

或者你可以：

SELECT *
FROM tbl_skus
WHERE SKU IN ('xxx-xxxx','yyy-yyyy')

#3

First query eventually becomes SELECT * FROM tbl_skus WHERE SKU ='xxx-xxxx' , the second - SELECT * FROM tbl_skus WHERE SKU IN ('xxx-xxxx,yyy-yyyy') (1 value passed to IN - it searches for SKU ='xxx-xxxx,yyy-yyyy'); to return 2 rows it must look ... IN ('xxx-xxxx','yyy-yyyy') (2 values passed).

第一个查询最终成为SELECT * FROM tbl_skus WHERE SKU ='xxx-xxxx'，第二个 - SELECT * FROM tbl_skus WHERE SKU IN（'xxx-xxxx，yyy-yyyy'）（1个值传递给IN - 它搜索SKU = 'XXX-XXXX，YYY-YYYY'）;要返回2行，它必须看... IN（'xxx-xxxx'，'yyy-yyyy'）（传递2个值）。

#1