How do I pass a list as a parameter in a stored procedure?

sql

table-valued-parameters

string-split

sql-injection

byAlex Kataev·Feb 13, 2025

Transmit a list to a stored procedure employing a Table-Valued Parameter (TVP). Define a type which resembles your list, use it in your procedure, and execute it with a table of the defined type.

Define Table Type:

CREATE TYPE ListType AS TABLE (Value INT); -- it's just a simple number table, not rocket science!

Stored Procedure:

CREATE PROCEDURE ProcessList @List ListType READONLY AS
SELECT * FROM @List; -- that's truly all it takes!

Execute with List:

DECLARE @ListVar ListType; -- declaring the variable
INSERT INTO @ListVar VALUES (1), (2), (3); -- filling it with stuff
EXEC ProcessList @List = @ListVar; -- run, Forrest, Run!

Benefits: Transfers lists swiftly, ensures strong typing, and supports batch operations.

Deep dive into structured Data Transmission

Passing a list of values requires strict structure, which is pivotal for accuracy and performance. Table Valued Parameters (TVPs) offer a robust method that lets you define the structure of the inbound data as a custom table.

Splitting Strings: Where and why

You might receive a simple list of values as a delimited string. Don't sweat, SQL Server 2016 and onwards got you covered with the STRING_SPLIT function. Here's how:

-- Assuming @UserIDs is cute comma-separated string of user IDs
SELECT * 
FROM Users
WHERE UserID IN (
    SELECT value 
    FROM STRING_SPLIT(@UserIDs, ',') -- spreading love, or rather, IDs!
);

However, for older versions of SQL Server or when STRING_SPLIT feels slow, a user-defined split function or the good old CHARINDEX can provide the much-needed comfort.

Performance: TVPs vs. STRING_SPLIT

TVPs often outrun string splitting for large datasets due to their shortcut nature and reduced parsing overhead. Yet, for smaller lists or where creating TVPs feels like overkill, string splitting could be a viable and may be even quicker approach.

Safety First: TVPs to the rescue

Using TVPs parts the sea of potential SQL injection risks, as data passed in is strongly typed and executed as anything but dynamic SQL. Using TVPs undoubtedly makes your application safer than threading the needle with dynamic SQL.

Plan B - Alternative methods and precautions

Now let's take a peek into some alternatives and safety measures when dealing with list parameters.

JSON and XML Query- A royal affair

Using JSON or XML data types lets you pass complex structured data. For JSON, there's OPENJSON, and for XML, there are rich handling capabilities; both indeed enable a royal SQL procession.

Top caps on lengths and types

Keep a tab on the maximum length for string parameters to avoid tripping on overly long strings. Try using strongly typed TVPs to avert potential data loss due to implicit conversions.

Calling reinforcements with C#

For .NET applications, a friendly C# method could play mediator between the application layer and the database; talk about team spirit, huh?

Azure to the fore

When using Azure DB or Azure Data Warehouse, methods like TVPs or native JSON and XML support ensure smooth sailing by standardizing data access across different platforms.

explain-codes / Sql / How do I pass a list as a parameter in a stored procedure?

Linked

T-sql split string based on delimiter



Limit on the WHERE col IN (...) Condition



Define variable to use with IN operator (T-SQL)



How do I split a delimited string so I can access individual items?



Passing multiple values for a single parameter in Reporting Services

