How can I get the name of a variable in SQL?-CodePudding

How can I get the name of a variable in SQL? something like C# nameof(variable)

In a SQL stored procedure, I handle a variable, @MetaData, which is a string repressing a JSON with a predefined structure:

{
  'Somekey1': 'Somevalue1',
  'Somekey2': 'Somevalue2'
}

Within the stored procedure, I check whether Somekey1 exists, and perform actions accordingly:

declare @existingSomekey1 VARCHAR(100) = (select [value]
from openjson(@Metadate) where [key] = 'Somevalue1')

Later, I do actions like:

set @Somekey1 = 'Some new value 1'
SELECT JSON_MODIFY(@Metadata, '$.Somekey1', @Somekey1)

So in the JSON_MODIFY command, if I provide the wrong path (which is the same as the variable name), it will create a different property altogether, which is wrong.
What's the best practice here to avoid typos in providing that path? which would lead to errors and exceptions...

CodePudding user response：

If I understand the question correctly, the goal is to prevent adding entry for non-existing path and this could be achieved by adding strict:

DECLARE @Metadata NVARCHAR(MAX) = '{"Somekey1": "Somevalue1",
                                    "Somekey2": "Somevalue2"}'
       ,@Somekey1 NVARCHAR(100) = 'Some new value 1';

SELECT JSON_MODIFY(@Metadata, '$.Somekey3', @Somekey1);

Output:

{ "Somekey1": "Somevalue1",
  "Somekey2": "Somevalue2",
  "Somekey3":"Some new value 1"}

Using strict:

SELECT JSON_MODIFY(@Metadata, 'strict $.Somekey3', @Somekey1)

Output:

Property cannot be found on the specified JSON path.

db<>fiddle live demo

CodePudding user response：

Not sure if this adds to the discussion, but ...

In 2016, the JSON key has to be a literal. However, in 2017 the key can be an expression

Example

DECLARE @Metadata NVARCHAR(MAX) = '{
  "Somekey1": "Somevalue1",
  "Somekey2": "Somevalue2"
}'

Declare @MyKey varchar(100),@MyVal varchar(100)

Select @MyKey = [key]
      ,@MyVal = [Value]
 From  OpenJson(@Metadata)
 Where [Key]='Somekey2'

Set @MyVal = 'This is a NEW Value';

SELECT JSON_MODIFY(@Metadata, '$.' @MyKey, @MyVal)

Results

{
  "Somekey1": "Somevalue1", 
  "Somekey2": "This is a NEW Value" 
}