SQL with Manoj

This post is part of the [Passing multiple/dynamic values to Stored Procedures & Functions] series, and as well as the new feature Native JSON support in SQL Server 2016.

Adding the fifth part to this series we will use JSON string that will contain the set of values and pass as an JSON param variable to the SP. Then inside the SP we will parse this JSON and use those values in our SQL Queries, just like we did in previous posts with CSV/XML strings:

USE [AdventureWorks2014]
GO

-- Create an SP with NVARCHAR type parameter for JSON string:
CREATE PROCEDURE uspGetPersonDetailsJSON (
	@persons NVARCHAR(MAX)
)
AS
BEGIN
	--DECLARE @persons NVARCHAR(MAX)
	--SET @persons = '{"root":[{"Name":"Charles"},{"Name":"Jade"},{"Name":"Jim"},{"Name":"Luke"},{"Name":"Ken"}]}'
	
	SELECT Name
	INTO #tblPersons
	FROM OPENJSON (@persons, '$.root')
	WITH ( 
		Name NVARCHAR(100)
	)

	SELECT 
		BusinessEntityID, 
		Title, 
		FirstName, 
		MiddleName, 
		LastName, 
		ModifiedDate
	FROM [Person].[Person] PER
	WHERE EXISTS (
		SELECT * 
		FROM #tblPersons tmp 
		WHERE tmp.Name  = PER.FirstName
	)
	ORDER BY FirstName, LastName

	DROP TABLE #tblPersons
END
GO

-- Create JSON string:
DECLARE @json NVARCHAR(1000)
SET @json = N'{
  "root": [
    { "Name": "Charles" },
    { "Name": "Jade" },
    { "Name": "Jim" },
    { "Name": "Luke" },
    { "Name": "Ken" }
  ]
}'

-- Use the JSON string as parameter which calling the SP:
EXEC uspGetPersonDetailsJSON @json
GO

-- Check the output, objective achieved

-- Final Cleanup
DROP PROCEDURE uspGetPersonDetailsXML
GO

Thus you can also use JSON string similar to the way you used XML string, to pass multiple and dynamic number of parameters to your Stored Procedures.

As JSON feature is new to SQL Server 2016, so this method will only work with SQL Server 2016 and above versions.