SQL Server 2017 - How to capture JSON output from a stored procedure that contains a temp table

Viewed 602

I have a stored procedure in my SQL Server 2017 database which is used by a node.js API. This stored procedure does some calculations, then returns a json object using FOR JSON PATH.

I now want to write some tests in T-SQL to ensure correct functioning of this stored procedure. Altering the stored procedure is not an option.

To reproduce the problem, suppose I have a stored procedure like this:

USE tempdb
GO
CREATE PROCEDURE dbo.example_sp 
AS
BEGIN
    SELECT val1, val2
    INTO #tmp
    FROM ( VALUES (1, 2), (3, 4)) vals (val1, val2)

    SELECT *
    FROM #tmp
    FOR JSON PATH
END

Now, noting that the FOR JSON PATH function will result in a single cell (1 row, 1 column) of type nvarchar, I would like to do something like this to extract the JSON object in order to test it:

CREATE TABLE #result (json_object varchar(max))

INSERT INTO #result 
    EXEC dbo.example_sp

But this produces an error:

The FOR JSON clause is not allowed in a INSERT statement.

OK, that's frustrating, but I can use the openrowset work around for that, right?

INSERT INTO #result 
    SELECT *
    FROM OPENROWSET('sqlncli', 'Server=(local);Trusted_Connection=yes;', 
                    'EXEC tempdb.dbo.example_sp')

No, now I have a this new error:

The metadata could not be determined because statement 'Select * from #tmp FOR JSON PATH' in procedure 'example_sp' uses a temp table.

Yet more frustrating, but surely I can use the WITH RESULT SET trick to solve that?

INSERT INTO #result 
    SELECT *
    FROM OPENROWSET('sqlncli', 'Server=(local);Trusted_Connection=yes;',
                    'EXEC tempdb.dbo.example_sp with result sets ((output varchar(max) NULL))')

Now I receive this error:

Cannot process the object "exec tempdb.dbo.example_sp with result sets (( output varchar(max) NULL))". The OLE DB provider "SQLNCLI11" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

OK, to deal with that we have to use SET NOCOUNT ON which permits SQL Server to skip some validation. The final version is:

INSERT INTO #result 
    SELECT *
    FROM OPENROWSET('sqlncli','Server=(local);Trusted_Connection=yes;',
                    'SET NOCOUNT ON; exec tempdb.dbo.example_sp with result sets (( output varchar(max) NULL))')

Which works at long last. I feel that this is way too much kludge for such a simple thing ... surely there is a better way to do this?

0 Answers
Related