Raw text output from stored procedure

[mortenlange]

Hi,
It would be nice if DAB could be forced not to format the output from stored procedures as JSON and instead just show the raw text output that the procedure returns.

Thing is that the JSON formatting is sometimes done server side in the stored procedure to need specific customization needs.
For example more advanced nested JSON structure that is required by an application as a part of the contract.

(In TSQL this is obtained by using the "FOR JSON PATH" clause.)

When DAB afterward does JSON formatting things go really wrong.

https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver16&tabs=json-path#output-of-the-for-json-clause

Such a feature would also make it easier to retrofit existing REST endpoints into DAB since same output can be guaranteed.

[abhishekkumams]

Hi, @mortenlange, could you share an example with DAB, that is causing issues for you due to how DAB formats results for stored-procedures?

[mortenlange]

Hi,
Yes of course. Here is a definition of a stored procedure that is defined in TSQL:

CREATE OR ALTER PROCEDURE dbo.DABJSONFormattingtest
AS
SET NOCOUNT ON;
DECLARE @MyResponse as NVARCHAR(MAX)
=
(
	SELECT 
	TOP 3
		CustomerID,
		CustomerGUID
	FROM 
		dbo.DABOutputTest
	ORDER BY 
		CustomerID ASC 
	FOR JSON PATH /*THIS KEYWORD FORMATS THE OUTPUT AS JSON */
)

SELECT 
@MyResponse AS Response
GO

When invoked it directly (for example in SMSS or from another application) it returns this text:
[{"CustomerID":1,"CustomerGUID":"71FEF8D2-C7B3-4C5D-A1EA-A64F11C5C687"},{"CustomerID":2,"CustomerGUID":"2856DBE5-5520-4520-9303-5481DAF805CF"},{"CustomerID":3,"CustomerGUID":"A94B53D6-0996-4029-AB6F-EE90F588EDEE"}]

If I set up DAB to execute the stored procedure in a GET method this text is returned:

{"value":[{"Response":"[{"CustomerID":1,"CustomerGUID":"71FEF8D2-C7B3-4C5D-A1EA-A64F11C5C687"},{"CustomerID":2,"CustomerGUID":"2856DBE5-5520-4520-9303-5481DAF805CF"},{"CustomerID":3,"CustomerGUID":"A94B53D6-0996-4029-AB6F-EE90F588EDEE"}]"}]}

Two differences:
a) The whole response is put into an array "value".
b) All the formatting (done by TSQL 'FOR JSON PATH'- KEYWORD) is put into escape characters.

This is the definition of the entity from my DABConfig.JSON

"entities": {
	"DABJSONFormattingtest": {
		"source": {
			"object": "[dbo].[DABJSONFormattingtest]",
			"type": "stored-procedure"
		},
		"rest": {
			"enabled": true,
			"methods": [
				"get"
			]
		},
		"permissions": [
			{
				"role": "anonymous",
				"actions": [
					{
						"action": "execute"
					}
				]
			}
		]
	}
}

I hope that this will bring some clarification - otherwise just ask for further details :-)