SQL Server 2017 Developer's Guide

Processing data from a comma-separated list of values

The following code example demonstrates how to use the OPENJSON rowset function to return the details of orders for IDs provided in a comma-separated list:

USE WideWorldImporters; 
DECLARE @orderIds AS VARCHAR(100) = '1,3,7,8,9,11'; 
SELECT o.OrderID, o.CustomerID, o.OrderDate  
FROM Sales.Orders o 
INNER JOIN (SELECT value FROM OPENJSON('[' + @orderIds + ']' )) x ON x.value= o.OrderID; 

Here is the list of orders produced by the preceding query:

OrderID     CustomerID  OrderDate
----------- ----------- ----------
1           832         2013-01-01
3           105         2013-01-01
7           575         2013-01-01
8           964         2013-01-01
9           77          2013-01-01
11          586         2013-01-01

In this example, the input argument is wrapped with square brackets to create a proper ...