Sending and returning JSON to accommodate complex types.

ReturningJson.sql

BEGIN TRANSACTION

GO
CREATE PROCEDURE FetchObjectsWithMetadata
    @SortBy NVARCHAR(50),
    @PageSize INT = 10,
    @PageNumber INT,
    @NameFilter NVARCHAR(256)
AS
BEGIN
    DECLARE @TotalCount INT, @PageHasNext BIT

    -- Calculate total count for the filter
    SELECT @TotalCount = COUNT(*)
    FROM sys.objects
    WHERE name LIKE '%' + @NameFilter + '%'

    -- Determine if there's a next page
    SET @PageHasNext = IIF(@PageSize * @PageNumber < @TotalCount, 1, 0)

    -- Prepare SQL with CTE
    ;WITH FilteredObjects AS (
        SELECT *, ROW_NUMBER() OVER (ORDER BY 
            CASE @SortBy
                WHEN 'name' THEN sys.objects.name
                WHEN 'create_date' THEN CAST(sys.objects.create_date AS VARCHAR(10))
                ELSE sys.objects.name -- Default column
            END) AS RowNum
        FROM sys.objects 
        WHERE name LIKE '%' + @NameFilter + '%'
    )
    SELECT
        (SELECT
            @TotalCount AS TotalCount,
            @PageSize AS PageCount,
            @PageNumber AS PageNumber,
            @PageHasNext AS PageHasNext,
            IIF(@PageHasNext = 1, @PageNumber + 1, NULL) AS PageNextNumber
         FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER) AS Metadata,
        (SELECT * 
         FROM FilteredObjects
         WHERE RowNum BETWEEN ((@PageNumber - 1) * @PageSize + 1) AND (@PageNumber * @PageSize)
         FOR JSON PATH, INCLUDE_NULL_VALUES) AS Results
END

GO

-- Declare variables for JSON results
DECLARE @results TABLE (Metadata NVARCHAR(MAX), Data NVARCHAR(MAX))

-- Call the stored procedure
INSERT INTO @results (Metadata, Data)
EXEC FetchObjectsWithMetadata 
    @SortBy = 'name', 
    @PageSize = 10, 
    @PageNumber = 1, 
    @NameFilter = 'sys'

DECLARE @Metadata NVARCHAR(MAX);
DECLARE @Data NVARCHAR(MAX);
SELECT TOP 1 @Metadata = Metadata, @Data = Data FROM @results;

DECLARE @PageCount INT;
SET @PageCount = JSON_VALUE(@Metadata, '$.PageCount');

SELECT @PageCount AS PageCount;

SELECT * 
FROM OPENJSON((SELECT Data FROM @results), '$')
WITH (
    name NVARCHAR(128),
    type_desc NVARCHAR(60),
    create_date DATETIME,
    is_ms_shipped BIT,
    is_published BIT,
    is_schema_published BIT,
    RowNum INT
)

ROLLBACK

SendingJson.sql

BEGIN TRANSACTION

GO
CREATE PROCEDURE SampleProcedure
    @JsonParameters NVARCHAR(MAX)
AS
BEGIN

    -- Extract SortBy value
    DECLARE @SortBy NVARCHAR(50), @Sql NVARCHAR(MAX)
    SET @SortBy = JSON_VALUE(@JsonParameters, '$.SortBy')

    SELECT
          Name
        , Age
        , DATEPART(year, DATEADD(year, -Age, GETDATE())) AS BirthYear
    FROM OPENJSON(@JsonParameters, '$.Values')
    WITH (
          Name NVARCHAR(50) '$.Name'
        , Age INT '$.Age'
    )
    ORDER BY 
        CASE WHEN @SortBy = 'Name' THEN Name END,
        CASE WHEN @SortBy = 'Age' THEN CONVERT(NVARCHAR, Age) END;
END;
GO

-- Execute with new JSON structure including SortBy
DECLARE @arguments NVARCHAR(MAX) = N'
	{
		"SortBy":"Name", 
		"Values": [ 
			{"Name":"John", "Age":30}, 
			{"Name":"Jane", "Age":25}
		]
	}';
EXEC SampleProcedure @arguments;
GO

ROLLBACK