cancel
Showing results for 
Search instead for 
Did you mean: 
Workflow Hero

Use Sql Where with a collection

Hi Everyone

I have a sharepoint list which retutns me some name; I store this name in a collection

After that; I'd like to execute a sql querry like that:

Select [Role], [Name],[Incoming]

FROM MyDB

WHERE Name IN mycollection

When I execute this WF; I get the following error message: Incorrect Syntax near 'Bob' (Bob is a name which is in my collection)

Any idea of what I'm doing wrong?

Thanks

0 Kudos
Reply
3 Replies
Workflow Hero

Re: Use Sql Where with a collection

The SQL syntax for a where .. in is

Where Name in ('Firstname','Secondname','Thirdname')

So you would need to Join your collection values with ',' into a string and add (' at the start and ') at the end

You should then be able to use this string in your Where expression

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: Use Sql Where with a collection

Sorry for the delay, I built a string and I get the following string:

 

('Bob','Julien','Tom')

So it's exactly what I need but during my querry I get the following error message:

Incorrect syntax near 'Bob'.

And here my query :

SELECT [Role]
,[Name]
FROM [dbo].[Stakeholders]
WHERE '{CommonSmiley TonguerojectUID}' = ProjectUID AND [Name] IN {WorkflowVariable:MName}

If I replace  {WorkflowVariable:MName} by  ('Bob','Julien','Tom')

Any idea of what I'm doing wrong?

0 Kudos
Accept as Solution Reply
Workflow Hero

Re: Use Sql Where with a collection

Try this

set MName to 'Bob','Julien','Tom'

Then make your query

SELECT [Role]
 ,[Name]
 FROM [dbo].[Stakeholders]
 WHERE '{CommonSmiley TonguerojectUID}' = ProjectUID AND [Name] IN ({WorkflowVariable:MName})

That is, make the brackets part of the query and not part of the string

0 Kudos
Accept as Solution Reply