Using SQL Server variables in clause

Question

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'

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

user2062108 · Accepted Answer · 2015-06-25 20:06:22Z

2

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);

answered Jun 25, 2015 at 20:06

user2062108

Sign up to request clarification or add additional context in comments.

Comments

Stephan · Accepted Answer · 2015-06-25 18:58:30Z

1

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

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')

answered Jun 25, 2015 at 18:58

Stephan

6,0581 gold badge19 silver badges26 bronze badges

Comments

a1ex07 · Accepted Answer · 2015-06-25 18:41:24Z

0

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).

answered Jun 25, 2015 at 18:41

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Collectives™ on Stack Overflow

Using SQL Server variables in clause

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related