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

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

Edit:

CodePudding user response：

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;

CodePudding user response：

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