SQL STRING_AGG() to generate sql string dynamically with multiple variables-CodePudding

This is an issue I am facing while using STRING_AGG.

Lets say I have a table, #table_A (This is a temp table)

   ID     CheckColumn
   1        GJAHR
   2        LMNON

Then I declare two variables viz.

   DECLARE @v1_YYYY varchar(4)
   DECLARE @v2_MM   varchar(4)
   SET @v1_YYYY = '2022'; 
   SET @v2_MM ='07';

Then I am trying to generate a string like below:

   GJAHR EQ @v1_YYYY AND LMNON EQ @v2_MM

I know I can use STRING_AGG() like below

   DECLARE @sql varchar(100);
   SELECT @sql = STRING_AGG(x.checkColumn   'EQ'   ''''   @v1_YYYY   '''' , ' AND ') 
                 FROM  #table_A

But this would create the below string:

   GJAHR EQ @v1_YYYY AND LMNON EQ @v1_YYYY

What I am missing here?

CodePudding user response：

You can use case expression to check the ID value and decide which variable to participate in the concatenation

   SELECT @sql  = STRING_AGG(checkColumn   'EQ'   '''' 
                  case when ID = 1 then @v1_YYYY else @v2_MM end
                  '''' , ' AND ') 
   FROM  #table_A