CODEWITHSUNDEEP
sql-servert-sql
shiji
shiji

Reputation: 79

How to store the result of exec in a variable

How can I store the result of exec in a variable? The output is JSON. My SQL query is complex and dynamically generated, so I have to set a variable and execute it.

create PROCEDURE dbo.RetrievePerfCounterData @jsonOutput NVARCHAR(MAX) OUTPUT
AS    
BEGIN 
    declare @sql NVARCHAR(MAX)
    SET @sql = ' SELECT TOP (1) getdate() AS ''dateTime'' ,suser_sname()AS ''user'' FOR JSON PATH  '

    exec (@sql)
END

Here's my attempt at storing the data in a variable:

DECLARE @json AS NVARCHAR(MAX)
EXEC dbo.RetrievePerfCounterData @jsonOutput = @json OUTPUT

Upvotes: 2

Views: 2079

Answers (2)

Rade Vojvodic
Rade Vojvodic

Reputation: 144

DECLARE @myVar VARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1
CREATE TABLE #t1 (col1 INT, col2 INT)
INSERT INTO #t1
SELECT 1, 1
UNION
SELECT 1, 2

SET @SQL = 'SET @myVar = (SELECT * FROM #t1 AS T FOR JSON AUTO);' 
EXEC sp_executesql @SQL, N'@myVar VARCHAR(MAX) OUT', @myVar OUT

SELECT @myVar

Upvotes: 1

Thom A
Thom A

Reputation: 95561

You need to use a subquery:

SET @json = (SELECT TOP (1) getdate() AS [dateTime],suser_sname()AS [user] FOR JSON PATH);

Upvotes: 0

Related Questions