web stats
Mirth Community - View Single Post - Need to pass delimited string for IN clause
View Single Post
  #1  
Old 10-08-2019, 07:55 AM
clarksss clarksss is offline
OBX.2 Kenobi
 
Join Date: Jul 2013
Posts: 76
clarksss is on a distinguished road
Exclamation Need to pass delimited string for IN clause

Here is my Mirth Code:
Code:
var si = dbConn.executeCachedQuery("EXEC Profile.pListSpecialIndicators NULL, NULL, '" + ruleBuilderProfileLocations + "', '" + lastTimePolledDateTime + "'");
Here is the WHERE clause using IN, where this is failing:
Code:
WHERE reg.Location_MisLocID in (@RuleBuilderProfileLocations)
the ruleBuilderProfileLocations is a comma delimited string that looks like this:

'HLSIDHS','KWHINNJ','HJIIKLL','XODIMSL','KWHINNJ', 'HJIIKLL','KWHINNJ','HJIIKLL','KWHINNJ','HJIIKLL', 'KWHINNJ','HJIIKLL','KWHINNJ','HJIIKLL','KWHINNJ', 'HJIIKLL','KWHINNJ','HJIIKLL','KWHINNJ','HJIIKLL', 'KWHINNJ','HJIIKLL','KWHINNJ','HJIIKLL','KWHINNJ', 'HJIIKLL','KWHINNJ','HJIIKLL'

I have tried passing this string over as a parameter in the stored procedure, using double quotes, but get this error:

The identifier that starts with 'HLSIDHS','HJIIKLL','XODIMSL','KWHINNJ','HJIIKLL', 'KWHINNJ','HJIIKLL','KWHINNJ','HJIIKLL','KWHINNJ', 'HJIIKLL','KWHINNJ','HJIIKLL','KWHINNJ','HJIIKLL', 'KWHINNJ','HJIIKLL','KWHINNJ','HJIIKLL' is too long. Maximum length is 128.

I have tried removing the quotes from each delimited item and passing that over as one string like this:
'HLSIDHS'HJIIKLL,XODIMSL,KWHINNJ,HJIIKLL,.....'
but that didn't seem to work.

I was hoping someone has tried to do this before and found a solution.
let me know if this is doable... I think it should be, and maybe I am over thinking things.

thanks in advance.

Last edited by clarksss; 10-08-2019 at 08:08 AM.
Reply With Quote