How to pass CSV to a stored procedure and use it in WHERE IN?

Question

I have a stored procedure, I want to pass a CSV to it and use this csv in a WHERE IN clause.

I have done it by passing XML to the stored procedure like this:

WHERE MyColumn IN (SELECT AOS.s.value('(./text())[1]', 'bigint')  
                   FROM (VALUES(@XML))V(X)  
                   CROSS APPLY  
                       V.X.nodes('/ArrayOfLong/long') AOS(s))

Result-wise, this stored procedure works fine, but it's slow. I want to improve the performance. When I run this stored procedure and get the execution plan, I get a warning.

I have also followed this answer, but I cannot use this solution due to permission issues, as mentioned in the comments.

I am looking for a simple clean and optimized solution.

Parameters should be like this '1,2,3,4,5,6,7' and it will be used like this WHERE IN (1,2,3,4,5,6,7)

Edit:

That's a screenshot of the version of SSMS (which is oddly 18.4, why haven't you updated in almost 3 years?); that doesn't tell me what version of SQL Server you are using. — Thom A, Aug 26 '22 at 11:34
@Larnu Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64) May 29 2022 15:55:47 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor) — Vivek Nuna, Aug 26 '22 at 11:36
I'm confused: do you have a CSV or XML? If you are creating XML from the CSV can you show how you do that? You could use a Table Valued Parameter, if you give it an index that is likely to be the fastest option. — Charlieface, Aug 26 '22 at 11:43
It says Invalid object name 'STRING_SPLIT'. when I run this 'SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');' @Larnu — Vivek Nuna, Aug 26 '22 at 11:44
@Charlieface I have CSV in the code, I have converted it to XML to pass to the SP. Will it solve the issue which I am facing mentioned in the above screenshot — Vivek Nuna, Aug 26 '22 at 11:46
What compatibility level is your database in, @viveknuna (and why is it 120 or lower)? — Thom A, Aug 26 '22 at 11:47

score 0 · Accepted Answer · answered Aug 26 '22 at 12:02

0

You should use a Table Valued Parameter for this.

First, create a table type. I usually keep a few standard ones. In this case you probably want a primary key, which gets you a free index.

CREATE TYPE dbo.IdList AS TABLE (Id int PRIMARY KEY);

Then you use it in your procedure like this

CREATE OR ALTER PROC dbo.YourProc
    @ids dbo.IdList READONLY
AS

SELECT s.Something
FROM Somewhere s
WHERE s.MyColumn IN (
    SELECT i.Id FROM @id i
);

You call it like this from T-SQL

DECLARE @tmp dbo.IdList;
INSERT @tmp (Id) VALUES
(1),
(2),
(3);

EXEC YourProc @ids = @tmp;

In client applications, there is normally special handling for TVPs. Use those rather than injecting INSERT statements into your query.

You may also need to add permissions for non-admin users

GRANT EXECUTE ON TYPE::dbo.IntList to db_datareader;

answered Aug 26 '22 at 12:02

Charlieface

52,284
6
19
43

Why do we need to add permission? – Vivek Nuna Aug 26 '22 at 12:19
@Do you know how to pass this parameter from C# code to this SP? – Vivek Nuna Aug 26 '22 at 12:42
You need to add permissions because by default you don't get `EXECUTE` on any table types. Yes, there are many tutorials, see here for example https://stackoverflow.com/a/66067309/14868997 – Charlieface Aug 26 '22 at 17:53

score 0 · Answer 2 · answered Aug 26 '22 at 14:31

0

Load the CSV into a table using SSIS and then just join your original table to the newly loaded table with the CSV data.

answered Aug 26 '22 at 14:31

planetmatt

407
3
10

How to pass CSV to a stored procedure and use it in WHERE IN?

2 Answers2