CODEWITHSUNDEEP
jsont-sqlstored-proceduresreturn-valuesql-server-2016
Oleg Volkov
Oleg Volkov

Reputation: 189

T-SQL stored procedure result into variable

I have a stored procedure for some selection in JSON.

CREATE PROC [pr_MySP]
    -- params
WITH ENCRYPTION
AS
BEGIN
    SELECT 
...
    FOR JSON PATH;
END

And now I want to use this stored procedure in another.

CREATE PROC [pr_MySP_1]
    -- params
WITH ENCRYPTION
AS
BEGIN
    DECLARE @result_sp NVARCHAR(MAX);
    EXEC @result_sp = [pr_MySP];
    SELECT @result_sp;
END

But when I try SELECT @result_sp; it return 0. What am I doing wrong?

Upvotes: 1

Views: 3445

Answers (2)

John Woo
John Woo

Reputation: 263803

You can store it in table variable instead.

DECLARE @result_sp (results NVARCHAR(MAX))

INSERT INTO @result_sp
EXEC [pr_MySP]

SELECT TOP 1 results FROM @result_sp

and if you still wanted it to store in a normal variable,

DECLARE @final NVARCHAR(MAX)
DECLARE @result_sp (results NVARCHAR(MAX))

INSERT INTO @result_sp
EXEC [pr_MySP]

SELECT TOP 1 @final = results FROM @result_sp

SELECT @final

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

I do not know what else you are doing within your first SP, but this might be better solved within an inlineable UDF:

CREATE FUNCTION dbo.CreateJSON()
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN (SELECT TOP 10 * FROM sys.objects FOR JSON AUTO);
END
GO

--You can use the UDF in any context. You can define parameters to control the behaviour

DECLARE @TheJson NVARCHAR(MAX);
SET @TheJson=dbo.CreateJSON();
SELECT @TheJson;
GO

--Clean up

DROP FUNCTION dbo.CreateJSON;

In most cases an inline TVF is better in performance! But you'd have to join its resultset, which is not so intuitive...

Upvotes: 2

Related Questions