Passing arrays to T-SQL procedures as JSON

Introduction

SQL Server do not supports complex types such as arrays or lists. If you need to send some parametrized query or execute stored procedure, you will need to use primitive scalar types. Currently, it is hard to send some structured information to SQL server modules.

New OPENJSON function can be used to parse array elements. If you need to send an array of element to SQL server it is better that dynamic SQL, and also simpler than table value functions.In this blog post you can see how to use this function.

In this post you will see how you can use JSON arrays to send complex data to SQL Server.

Problem

Multi-select list or check box group is one of the most commonly used UI components. They are used when user needs to select set of values. As an example, in the following checkbox group user can select some options:

Imagine that you need to create a report with all items that are related to these options or to take all details about the selected options using this checkbox list. You would need to execute something like following query:

SELECT * FROM Options WHERE OptionId IN (4,25)

Assumption is that 4 and 25 are ids of selected options. Now the problem is - how you can send an array of numbers to SQL Server? You don't have a built-in support for array of numbers or strings. Currently you have following options:

Create T-SQL query in app layer

 One (not recommended) approach would be to get parameters/selected ids and build SQL query in application layer (e.g. C#):

 var options = GetOptionIds();
 var sql = String.Format(
           "SELECT * FROM Options WHERE OptionId IN ({0})",
          options);

However, if you do it this way there is a high risk that you hit some SQL injection attack. In web applications, if you just read values from HTTP parameter collection without some escaping, someone might inject some dangerous SQL that will be executed when you concatenate dynamically created SQL string.

Unfortunately, since list of strings is not a type you cannot parametrize it and let ADO.NET to escape special characters.

Send ids as a string list to SQL Server

Another similar approach would be to pass list of ids as a string to SQL Server, build dynamic text in SQL server procedures using EXECUTE sp_executesql. However, this "solution" has the same problem as previous one. Someone might send dangerous SQL script as a parameter, and dynamically created SQL might delete some objects.

If you really want to pass coma separated list you might try to split a string to a table using T-SQL using some table-valued function. However, this is not perfect solution because it will fail if you want to pass a list of string values where some to the string values might contain comma e.g. "John","Adam","Mark, Jr","Michael". Unfortunately, simple split function is not context aware so you cannot know does some comma (,) represents separator or part of some string.

Use table value parameters

Proper approach would be to use Table-Valued Parameters in SQL Server 2008 (ADO.NET). The idea is that you can create table type in SQL server that will be input parameter of stored procedure and pass list of ids as a strongly typed collection of values. Although this is proper approach it might be to heavy just to pass one simple list, and also it works fine only if you have C# clients (I think that there are active requests to add support for TVPs in other languages). If you are not familiar with this approach I would recommend to take a look at the MSDN how to do it.

New approach - use JSON array

 SQL Server 2016/Azure Sql Db introduce new table value function OPENJSON that can be used to transform array of JSON objects into a table where each returned row will have one JSON object. As an example we can execute the following query:

 SELECT value FROM OPENJSON('[1,4,6,9]')

Result of this query will be:

You can see that simplest JSON arrays are almost identical to comma separated strings - you just have a wrapper with brackets. Since JSON array is very similar to comma separated list, we can use this function to parse a list of ids in a stored procedure:

 create procedure
dbo.GetOptionList(@pOptionIds nvarchar(100))
 as begin

 SELECT id, title
 FROM Option 
         JOIN OPENJSON(@pOptionIds)
                ON OptionId = value
  -- Equivalent to "WHERE OptionId IN (1,4,6,9)"

end

GO
EXEC dbo.GetOptionList '[1,4,6,9]'

There is no dynamic T-Sql or SQL injection because whatever is a value in JSON array it will not be "executed" - you will just try to lookup options by id. Also, it is better than regular split using table valued functions because it is context aware. If you have a list of comma separated strings where some strings may contain comma (e.g.["John","Adam","Mark, Jr","Michael"], OPENJSON will not split "Mark, Jr" as two items because it knows that this comma is not JSON separator.

Conclusion

Although new OPENJSON function should be used to parse JSON arrays, is some simple cases you can use it as a nice replacement for STRING_SPLIT.

If you really need to to pas a list of comma separated values to SQL server and you don't want to use table valued parameters, please do not use dynamic SQL! This workaround is more secure than dynamic SQL and it is even easier that some user defined table value function that split strings by some delimiter. I still belive that table valued parameters are the proper approach, but if you don't want to use them - use this function.

This function will be available in SQL Server 2016 CTP3 and Azure Sql Db in November 2015 so I hope that it can help you.