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

Question

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.

Niranjan Rajawat · Accepted Answer · 2017-11-22 05:48:25Z

2

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

answered Nov 22, 2017 at 5:48

Niranjan Rajawat

5632 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related