How do I pass multiple values to parameter?

Question

I am trying to pass a single or multiple input variable in my select statement

I tried using an IN statement

DECLARE @LOANKIND VARCHAR(100)
SET @LOANKIND = 'Payables - Loan,Payables - Lines of Credit'
SELECT * from Table
WHERE LOANKIND.KIND_DESC IN (@LOANKIND)

All of my possible values are:

Payables - Loan,Payables - Lines of Credit,Payables - Recoverable Grant,Payables - Grant,Payables - Bonds,Payables - Guarantee,Receivables - Loan,Receivables - Lines of Credit,Receivables - Recoverable Grant,Receivables - Guarantee

Possible duplicate of [Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) — Andrei Odegov, May 22 '19 at 15:43
Depending on your sqlserver version use XML or user defined table type — Serg, May 22 '19 at 15:43
You have to put each of the possible values in single quotes, else they won't be recognized as multiple separate values, but as a single string. Something like this: `"'Payables - Loan', 'Payables - Lines of Credit', 'Payables - Recoverable Grant'"`. — MicSim, May 22 '19 at 15:53
Are your values in one table? If yes, you can do a subquery to get al the values you need in the "IN" — Antonio Avndaño Duran, May 22 '19 at 16:01
The safe solution is to use a table-valued parameter and join the TVP with the table. Dynamic SQL is faster but can lead to SQL injection attacks, or problems simply because one of the values contained a quote or some other unfortunate character. Eg `Publishers - O'Reily,Publishers - Addison-Wesley` — Panagiotis Kanavos, May 22 '19 at 16:13
Another possibly safe solution would be to pass an XML or JSON string and use the built-in XML or JSON parsing functions to extract the values as a table and join with it. The result will be similar to the TVP — Panagiotis Kanavos, May 22 '19 at 16:16

Md. Suman Kabir · Accepted Answer · 2019-05-22T16:08:07.907

1

You can use dynamic query for this. Like below :

DECLARE @sql nvarchar(max)
DECLARE @LOANKIND VARCHAR(100)
SET @LOANKIND = '''Payables - Loan'', ''Payables - Lines of Credit'''
SET @sql = 'SELECT * from Table WHERE LOANKIND.KIND_DESC IN (' + @LOANKIND + ')'

SP_EXECEUTESQL @sql

edited May 22 '19 at 16:08

answered May 22 '19 at 15:43

Md. Suman Kabir

5,243
5
25
43

Don't forget to declare @sql as well – Gen Wan May 22 '19 at 16:03
1

This looks like exactly what I need but the sql variable is not returning any rows. I believe the LOANKIND variable is not matching exactly whats in my field. Even though I matched to string exactly. – Chris May 22 '19 at 17:16

score 0 · Answer 2 · answered May 22 '19 at 18:29

Another way.

If you have SQL 2016+ you can use STRING_SPLIT. Otherwise you can download DelimitedSplit8K.

-- Sample Data
DECLARE @Table TABLE (KIND_DESC VARCHAR(100));
INSERT  @Table(KIND_DESC) VALUES ('Payables - Pizza'),('Payables - Loan'),('Payables - Lines of Credit');

-- Solution
DECLARE @LOANKIND VARCHAR(100) = 'Payables - Loan,Payables - Lines of Credit'

SELECT * 
FROM @Table AS LOANKIND
JOIN STRING_SPLIT(@LOANKIND,',') AS s
ON   LOANKIND.KIND_DESC = s.[value];

How do I pass multiple values to parameter?

2 Answers2