How to use declared variable for SQL statement by using "IN" condition

Question

I am trying to write some code but it doesn't work. It only those TypeT description. How can I display the TypeT and TypeS condition for below case?

DECLARE @PaymentType VARCHAR(20) = '';

SELECT @PaymentType = LISTID 
FROM LIST 
WHERE LISTCONFIG IN ('TypeT', 'TypeS')

SELECT * 
FROM LIST 
WHERE LISTID IN  (@PaymentType)

thanks for the information. – user3835327 Jun 06 '17 at 07:56 — user3835327, Jun 06 '17 at 07:56

Azar · Answer 1 · 2017-06-06T07:48:42.497

2

Try the below

SELECT * FROM LIST 
WHERE LISTID IN  (select LISTID FROM LIST WHERE LISTCONFIG IN 'TypeT','TypeS'))

Or you can use the below in only sql server 2016

DECLARE @PaymentType VARCHAR(20)
SELECT @PaymentType= isnull(@PaymentType+',','')+LISTID FROM list WHERE LISTID IN ('TypeT','TypeS')
SELECT * FROM LIST WHERE LISTID IN  (select value from STRING_SPLIT(@PaymentType, ',')  )

edited Jun 06 '17 at 07:48

answered Jun 06 '17 at 07:37

Azar

1,852
15
17

hi, sorry i want the Types to be store in variable ..so i can use that variable for other purpose. – user3835327 Jun 06 '17 at 07:40
We can only go with one at a time with a variable or you have to store them with delimiter seperated and then split them based on delimiter or dynamic sql. – Azar Jun 06 '17 at 07:43
thanks for the reply.. i've found the solution ! – user3835327 Jun 06 '17 at 08:05

score 1 · Answer 2 · answered Jun 06 '17 at 07:43

1

Try:

SELECT * 
FROM LIST 
WHERE 
    LISTID IN  (SELECT LISTID FROM LIST WHERE LISTCONFIG IN ('TypeT','TypeS'))

However, I think that this is still too complex. Have you tried:

SELECT *
FROM LIST WHERE LISTCONFIG IN ('TypeT','TypeS')

answered Jun 06 '17 at 07:43

Peter Smith

5,528
8
51
77

thanks for the reply.. i've found the solution ! – user3835327 Jun 06 '17 at 08:05

score 1 · Answer 3 · answered Jun 06 '17 at 07:47

1

If you want to do it using IN, you'll have to do it with dynamic SQL. This also means creating the comma separated string. Which is doable, but an easier way is this:

DECLARE @PaymentType TABLE (ListID INT) ;

INSERT @PaymentType (ListID)
SELECT LISTID FROM LIST WHERE LISTCONFIG IN ('TypeT','TypeS')

SELECT  LST.* 
FROM    LIST Lst
JOIN    @PaymentType AS PT
    ON  PT.ListID = Lst.ListID

answered Jun 06 '17 at 07:47

HoneyBadger

14,750
3
34
48

thanks for the reply.. i've found the solution ! – user3835327 Jun 06 '17 at 08:05

score 1 · Answer 4 · answered Jun 06 '17 at 07:54

1

Another sollution is using dynamic strings. If your sql version is below 2016

DECLARE @PaymentType VARCHAR(20)
SELECT @PaymentType= isnull(@PaymentType+',','')+LISTID FROM list WHERE LISTID IN ('TypeT','TypeS')
exec('SELECT * FROM LIST WHERE LISTID IN  ('+@PaymentType+')')

answered Jun 06 '17 at 07:54

Pasetchnik

318
1
9

thanks for the reply.. i've found the solution ! – user3835327 Jun 06 '17 at 08:06

How to use declared variable for SQL statement by using "IN" condition

4 Answers4