CODEWITHSUNDEEP
sqlsql-serversql-server-2016
Simon Peters
Simon Peters

Reputation: 53

How do you get the results of a SQL Server stored procedure in JSON?

I want to execute an existing stored procedure in SQL Server and get the results out in JSON.

Details: existing stored procedure dbo.pr_GetUser returns a recordset.

I want to create a wrapper that will do the following:

EXEC dbo.pr_GetUser 
FOR JSON AUTO

This will allow me to consume an existing stored procedure and not be concerned about the inner workings or modifications in logic.

Is this possible without editing the existing stored procedure?

Upvotes: 1

Views: 863

Answers (1)

SHD
SHD

Reputation: 409

You can do it like this,

INSERT INTO #table(userId int)
EXEC dbo.pr_GetUser

select userId  from #table FOR JSON Auto

Note that table structure of #table will be same as the result.

Upvotes: 2

Related Questions