Sending JSON to a Stored Procedure and Handling It in SQL Server

As more applications use complex data formats, it's useful to pass JSON data directly to a stored procedure in SQL Server and process it. This guide will show you how to do this, including examples with both simple and complex JSON data.

Why use JSON with SQL Stored procedures?

JSON (JavaScript Object Notation) is popular because it's simple and easy to work with. When connecting SQL Server with applications that use JSON (like web APIs), passing JSON to stored procedures has several advantages.

Compact and Easy: JSON is small and straightforward to read and write.
Flexible Structure: JSON can handle complex and nested data, making it great for intricate data relationships.
Built-In Tools: SQL Server has functions like JSON_VALUE and OPENJSON to help you work with JSON data.

Here’s how you can pass JSON to a stored procedure and process it in SQL Server.

Step 1. Sending Simple JSON Data to a Stored Procedure.

For basic use, JSON can be used to send simple values like numbers and text. Here's how to do it.

Create and Set JSON Data: In your SQL script or application code, create a JSON string and pass it as a parameter to the stored procedure.

DECLARE @JsonData NVARCHAR(MAX);

-- Create JSON data
SET @JsonData = N'{
    "LenderId": 1234,
    "ProvinceId": 56
}';
-- Pass JSON data to the stored procedure
EXEC SendSimpleJsonData @JsonInput = @JsonData;

Set Up the Stored Procedure to Handle JSON: The stored procedure will take the JSON input and use JSON_VALUE to get the specific fields from it.

CREATE OR ALTER PROCEDURE dbo.[SendSimpleJSONData]
    @JsonInput NVARCHAR(MAX)
AS
BEGIN
    -- Extract values from the JSON input
    DECLARE @LenderId INT, @ProvinceId INT;

    SET @LenderId = JSON_VALUE(@JsonInput, '$.LenderId');
    SET @ProvinceId = JSON_VALUE(@JsonInput, '$.ProvinceId');

    -- Use the extracted values for further processing
    SELECT @LenderId AS LenderId, @ProvinceId AS ProvinceId;
END;

In this example

JSON_VALUE retrieves single values from the JSON string.
$.LenderId and $.ProvinceId are JSON path expressions that help find specific fields in the JSON data.

Result

SQL

Step 2. Handling Nested JSON Data.

In real life, JSON data is often more complicated, with nested objects and arrays. SQL Server can handle these complex structures, so you can work with hierarchical data directly.

Here’s an example of how to manage nested JSON data.

Pass Nested JSON to the Stored Procedure: You can pass a JSON string containing nested objects as input.

DECLARE @JsonData NVARCHAR(MAX);

-- Create nested JSON data
SET @JsonData = N'{
    "Lender": {
        "LenderId": 1234,
        "Name": "ABC Bank"
    },
    "Location": {
        "ProvinceId": 56,
        "City": "Toronto"
    }
}';

-- Pass JSON data to the stored procedure
EXEC dbo.NestedJsonData @JsonInput = @JsonData;

Create the Stored Procedure to Handle Nested JSON: Inside the stored procedure, use JSON_VALUE and JSON_QUERY to get data from nested objects.

CREATE OR ALTER PROCEDURE dbo.[NestedJsonData]
    @JsonInput NVARCHAR(MAX)
AS
BEGIN
    -- Extract values from the nested JSON input
    DECLARE @LenderId INT, @LenderName NVARCHAR(100), @ProvinceId INT, @City NVARCHAR(100);

    -- Extract values from the nested "Lender" object
    SET @LenderId = JSON_VALUE(@JsonInput, '$.Lender.LenderId');
    SET @LenderName = JSON_VALUE(@JsonInput, '$.Lender.Name');

    -- Extract values from the nested "Location" object
    SET @ProvinceId = JSON_VALUE(@JsonInput, '$.Location.ProvinceId');
    SET @City = JSON_VALUE(@JsonInput, '$.Location.City');

    -- Use the extracted values
    SELECT @LenderId AS LenderId, @LenderName AS LenderName, @ProvinceId AS ProvinceId, @City AS City;
END;

Result

SQL Query

Step 3. Handling JSON Arrays.

Sometimes, JSON data includes arrays, like a list of locations. SQL Server’s OPENJSON function can parse these arrays and display them as rows in a table.

Here’s an example of JSON with an array of locations.

Pass JSON with Array Data

DECLARE @JsonData NVARCHAR(MAX);

-- Create JSON data with an array
SET @JsonData = N'{
    "Lender": {
        "LenderId": 1234,
        "Name": "ABC Bank"
    },
    "Locations": [
        { "ProvinceId": 56, "City": "Toronto" },
        { "ProvinceId": 78, "City": "Vancouver" }
    ]
}';

-- Pass JSON data to the stored procedure
EXEC [dbo].NestedArrayJSON @JsonInput = @JsonData;

Parse the JSON Array in the Stored Procedure: Use OPENJSON to parse and handle arrays.

CREATE OR ALTER PROCEDURE [dbo].[NestedArrayJSON]
    @JsonInput NVARCHAR(MAX)
AS
BEGIN
    -- Extract the Lender details
    DECLARE @LenderId INT, @LenderName NVARCHAR(100);
    SET @LenderId = JSON_VALUE(@JsonInput, '$.Lender.LenderId');
    SET @LenderName = JSON_VALUE(@JsonInput, '$.Lender.Name');

    -- Parse the "Locations" array
    SELECT ProvinceId, City
    FROM OPENJSON(@JsonInput, '$.Locations')
    WITH (
        ProvinceId INT '$.ProvinceId',
        City NVARCHAR(100) '$.City'
    );
END;

The OPENJSON function is used to convert the array of locations into a table format, allowing each object to be treated as a row in the result set.

Result

Table format

Conclusion

Passing and handling JSON in SQL Server stored procedures is a flexible way to manage structured data, especially for complex or nested data from applications or APIs. Whether you're dealing with simple JSON objects or more complicated nested structures and arrays, SQL Server’s JSON functions (like JSON_VALUE, JSON_QUERY, and OPENJSON) make it easy to parse and get the data you need.

Following the steps in this guide will help you use JSON effectively and keep your data processing tasks simple and efficient in SQL Server.

Key Points

Use JSON_VALUE to get single values from JSON.
Use JSON_QUERY to extract JSON sub-objects or arrays.
Use OPENJSON to turn JSON arrays into rows.

JSON in SQL Server is a powerful tool for handling complex data, helping you work better with modern applications and services. You can adjust these methods to fit your needs and start using JSON for more flexible stored procedures in SQL Server!

Happy Coding!