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?