SQL Server using in keyword pass string array query

Question

I don't think the IN clause can accept bind parameters with multiple values. Oracle can't and a couple minutes

and query is

declare @setting varchar(max)

set @setting ='''Sales Entry Grid Cursor'',''Customer Mandatory'',''Column Uom'',''Show Marka'',''Show Discount Amount In Grid'',''Show Discount % In Grid'',''Calculation based on Weight *rate'''

and stored procedure is

 Select pageconfig_action 
 From [RetailSoft].[dbo].[tbl_pageconfig] 
 Where [PageConfig_settingsName] in (@setting)
   and PageConfig_CompanyId = 1

result is empty

And pass string in directly in keyword

Select pageconfig_action 
From [RetailSoft].[dbo].[tbl_pageconfig] 
Where [PageConfig_settingsName] in ('Sales Entry Grid Cursor', 'Customer Mandatory', 'Column Uom', 'Show Marka', 'Show Discount Amount In Grid', 'Show Discount % In Grid', 'Calculation based on Weight *rate')
  and PageConfig_CompanyId=1

then result is ok

Read the excellent article by Erland Sommarskog on this topic - sommarskog.se/arrays-in-sql-2005.html. — Andrei Odegov
– Andrei Odegov, Commented Jan 29, 2019 at 10:37

marc_s · Accepted Answer · 2019-06-23 14:56:13Z

1

In SQL Server 2016+: using string_split built in function. Please note, that extra single quotes are not necessary anymore:

DECLARE @setting varchar(max)

set @setting ='Sales Entry Grid Cursor,Customer Mandatory,Column Uom,Show Marka,Show Discount Amount In Grid,Show Discount % In Grid,Calculation based on Weight *rate'


Select pageconfig_action from [RetailSoft].[dbo].[tbl_pageconfig] 
Where [PageConfig_settingsName] in(SELECT value FROM string_split(@setting, ',') )
 and PageConfig_CompanyId=1

If you run SQL Server older than SQL 2016, the answer of @GuidoG is a preferable method

edited Jun 23, 2019 at 14:56

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 29, 2019 at 10:31

Alexander Volok

5,9903 gold badges19 silver badges34 bronze badges

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

4 Comments

Thom A Over a year ago

Like the other answer, [PageConfig_settingsName] in( '+@setting+ ' ) is a vulnerability waiting to be exploited. That solution is far from recommended.

Alexander Volok Over a year ago

@Larnu, yeps, true, there is a warning in my answer ;)

Thom A Over a year ago

So include only methods that aren't exploitable. An answer that provides (such a large) a security flaw should be avoided.

Alexander Volok Over a year ago

I see, have no objections

marc_s · Accepted Answer · 2019-06-23 14:56:35Z

1

You need to make Setting a table, not a varchar.
Then there is no need for dynamic sql and you can keep it simple like this

declare @Setting table (name varchar(50))

insert into @Setting (name)
values ('Sales Entry Grid Cursor'), 
       ('Customer Mandatory'),
       ('Column Uom'),
       ('Show Marka'),
       ('Show Discount Amount In Grid'),
       ('Show Discount % In Grid'),
       ('Calculation based on Weight *rate')

Select pageconfig_action 
from   [RetailSoft].[dbo].[tbl_pageconfig] 
Where  [PageConfig_settingsName] in (select name from @setting)
and    PageConfig_CompanyId=1

edited Jun 23, 2019 at 14:56

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 29, 2019 at 10:26

GuidoG

12.3k6 gold badges59 silver badges101 bronze badges

Comments

marc_s · Accepted Answer · 2019-06-23 14:56:27Z

You're misunderstanding how strings are treated in SQL Server. The string you have ('''Sales Entry Grid Cursor'',''Customer Mandatory'',''Column Uom'',''Show Marka'',''Show Discount Amount In Grid'',''Show Discount % In Grid'',''Calculation based on Weight *rate''') is one literal value, not multiple values. IN won't "work" against it because it's looking for a row has that whole string's value for PageConfig_settingsName.

There are 2 options here. The first is to split your string and compare:

SELECT pageconfig_action
FROM [RetailSoft].[dbo].[tbl_pageconfig]
     CROSS APPLY STRING_SPLIT(@setting, ',') SS
WHERE [PageConfig_settingsName] = SS.[value]
  AND PageConfig_CompanyId = 1;

Note you don't needs the quotes around each utem (unless that really have those quotes in their value)

If you aren't on SQL Server 2016+ search delimitedsplit8k (If you're on 2008), or delimitedsplit8k_lead if you're on 2012/2014.

Otherwise, you can use a table-value variable and pass each value separately:

DECLARE @Setting table (setting varchar(255));
INSERT INTO @Setting (setting)
VALUES ('Sales Entry Grid Cursor'),
       ('Customer Mandatory'),
       ('Column Uom'),
       ('Show Marka'),
       ('Show Discount Amount In Grid'),
       ('Show Discount % In Grid'),
       ('Calculation based on Weight *rate');

SELECT P.pageconfig_action
FROM [RetailSoft].[dbo].[tbl_pageconfig] P
     JOIN @Setting S ON S.setting = P.[PageConfig_settingsName]
WHERE P.PageConfig_CompanyId = 1;

Collectives™ on Stack Overflow

SQL Server using in keyword pass string array query

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related