CODEWITHSUNDEEP
sqljsonsql-serverroot
DineshDB
DineshDB

Reputation: 6193

How to use Declared variables in JSON root, while convert table data to JSON in SQL server?

I am using SQL server.

I try to get JSON value from table data in SQL server. In my case I want to get ROOT as what I get from the Declared VARIABLE.

Here is my code:

DECLARE @TpiType VARCHAR(64)='SMS'
 ,@TpiName VARCHAR(24)='PLIVO'
 ,@Purpose VARCHAR(24)='SYSTEM'

DECLARE @Purpose2 VARCHAR(24)

SET @Purpose2=@Purpose +'%'

SELECT Name,Value 
FROM MyTable 
WHERE Type=@TpiType AND Name=@TpiName 
AND NAME LIKE @Purpose2
FOR JSON AUTO,ROOT(@TpiType)

When I try this it shows me the error:

Incorrect syntax near '@TpiType'.

But If I give the string value directly to the root, it works fine. But when I pass the variable, it throws error.

Please help me to fix this issue. Thanks in advance.

Upvotes: 1

Views: 762

Answers (1)

Niranjan Rajawat
Niranjan Rajawat

Reputation: 563

You might find using dynamic sql helpful here:

DECLARE @TpiType VARCHAR(64)='''SMS''' 
 ,@TpiName VARCHAR(24)='''PLIVO'''
 ,@Purpose VARCHAR(24)='''SYSTEM'

DECLARE @Purpose2 VARCHAR(24)

SET @Purpose2=@Purpose +'%'''

Declare @sql VARCHAR(2000)
Set @sql = 'SELECT Name,Value 
FROM MyTable 
WHERE Type=' +@TpiType+' AND 
Name=' +@TpiName +' 
AND NAME LIKE ' +@Purpose2+' 
FOR JSON AUTO,ROOT(' +@TpiType+')'

Print @sql -- check if this prints the sql script correctly
--Exec (@sql) -- run this to execute dynamically created script

Upvotes: 2

Related Questions