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

As far as I can tell, there are three main contenders: Table-Valued Parameters, delimited list string, and JSON string.

Since 2016, you can use the built-in STRING_SPLIT if you want the delimited route: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

That would probably be the easiest/most straightforward/simple approach.

Also since 2016, JSON can be passed as a nvarchar and used with OPENJSON: https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql

That's probably best if you have a more structured data set to pass that may be significantly variable in its schema.

TVPs, it seems, used to be the canonical way to pass more structured parameters, and they are still good if you need that structure, explicitness, and basic value/type checking. They can be a little more cumbersome on the consumer side, though. If you don't have 2016+, this is probably the default/best option.

I think it's a trade off between any of these concrete considerations as well as your preference for being explicit about the structure of your params, meaning even if you have 2016+, you may prefer to explicitly state the type/schema of the parameter rather than pass a string and parse it somehow.

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to stored-procedures

How to create temp table using Create statement in SQL Server? How do I pass a list as a parameter in a stored procedure? SQL Server IF EXISTS THEN 1 ELSE 2 Stored procedure with default parameters Could not find server 'server name' in sys.servers. SQL Server 2014 How to kill all active and inactive oracle sessions for user EXEC sp_executesql with multiple parameters MySQL stored procedure return value SQL Server: use CASE with LIKE SQL server stored procedure return a table