Home T-SQL stored procedure result into variable
Reply: 2

T-SQL stored procedure result into variable

Oleg Volkov
1#
Oleg Volkov Published in 2018-02-14 06:59:41Z

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?

Shnugo
2#
Shnugo Reply to 2018-02-14 07:47:47Z

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...

John Woo
3#
John Woo Reply to 2018-02-14 07:48:52Z

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.304303 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO