Azure ADF build query using concat in lookup activity

Question

I have below SQL server query which builds a string like this: 'Group1', 'Group2', 'Group3'

SELECT '''' + '
STRING_AGG(CAST(groupname as NVARCHAR(MAX)), ''',''') + ''''
FROM groups
WHERE category = 'food'

I have a lookup expression in a ADF pipeline where I want to build the query dynamic using the @concat function. This query is executed by the lookup expression in a SQL server database. The WHERE clause in the SQL statement needs to parameterized with a pipeline parameter. I tried below code but that does not work and gives me a error: missing comma between the arguments How can I fix this?

@concat('
  SELECT ''' + 
    STRING_AGG(CAST(groupname as NVARCHAR(MAX)), ''',''') + '''
  FROM groups
  WHERE category = ''',pipeline().parameters.Category,''''
  )

For any dynamic SQL question on any platform, the first thing to do is find out what SQL it generated. I suggest you look in the ADF logs and find out and go from there — Nick.Mc, May 03 '21 at 01:37
Hi @user7432713 Kindly let me know if you need more information. :) — Joseph Xu, May 07 '21 at 02:23
Hi @user7432713. If my answer is helpful for you, you can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. : ) — Joseph Xu, May 18 '21 at 07:12

Joseph Xu · Answer 1 · 2021-05-03T02:21:19.590

I created a simple test as follows:

create table dbo.groups(
    groupname varchar(25),
    category varchar(25)
)

insert into dbo.groups values
('Group1','food'),
('Group2','food'),
('Group3','food')

SELECT '''' + STRING_AGG(CAST(groupname as NVARCHAR(MAX)), ''',''') + '''' as groups
FROM groups WHERE category =  'food'

Add dynamic content @concat('SELECT '''''''' + STRING_AGG(CAST(groupname as NVARCHAR(MAX)), '''''','''''')+ '''''''' as groups FROM groups WHERE category = ''',pipeline().parameters.Category,'''') to Lookup actvity.
Debug result is as follows, I think it is what you want.

Azure ADF build query using concat in lookup activity

1 Answers1