0

This question already has an answer here:

declare @id int
set @id = 1754
select 
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."1754".value')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+cast(@id as varchar)+'"***.value')) Agency,
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."1754".value')) Protocal
from actions_new a
left join action_data ad on ad.id_ref = a.id
where baid = 32 and displayrequestid < 100;

Its working with sql server 2017 but error showing in sql server 2016.

Error : Msg 13610, Level 16, State 1, Line 3 The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal. Problem in converting INT to vachar "'+cast(@id as varchar)+'"

marked as duplicate by Erik Darling sql-server Aug 22 at 12:21

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

0

As answered for JSON_QUERY in this question the same is true for JSON_VALUE as well.

Docs on JSON_VALUE

In SQL Server 2017 (14.x) and in Azure SQL Database, you can provide a variable as the value of path.

Before SQL Server 2017 you would have to build the query dynamically.


Examples

Printing the query on sql server 2016

declare @id int;
set @id = 1754;

DECLARE @SQL VARCHAR(MAX);

SET @SQL ='
select 
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap.***"'+cast(@id as varchar(4))+'"***.value'')) Agency,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) Protocal
from actions_new a
left join action_data ad on ad.id_ref = a.id
where baid = 32 and displayrequestid < 100;';
PRINT (@SQL);

Executing the query on sql server 2016

declare @id int;
set @id = 1754;

DECLARE @SQL NVARCHAR(MAX);
DECLARE @Parameterdefinition nvarchar(20) ='@id int';
SET @SQL ='
select 
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap.***"+cast(@id as varchar(4))"***.value'')) Agency,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) Protocal
from actions_new a
left join action_data ad on ad.id_ref = a.id
where baid = 32 and displayrequestid < 100;';

EXEC SP_EXECUTESQL  
@SQL,@Parameterdefinition,@id;

DB<>Fiddle

Not the answer you're looking for? Browse other questions tagged or ask your own question.