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 JSON text and the OPENJSON function is invoked without the path argument. OPENJSON created one row for each element from the JSON array and that is exactly what you needed.