Announcement

Collapse
No announcement yet.

Need to pass delimited string for IN clause

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 08:08 AM.

  • #2
    Try putting () around RuleBuilderProfileLocations

    Comment


    • #3
      My Work

      that did not seem to work either, assuming I put them in the correct places... do you have an example by any chance (in case I am doing it wrong)??

      Comment


      • #4
        I don't. The problem that you are having is that your sp is taking the commas and considering them as different parameters. Try this...

        Create an array and push the items on. In your sp you may have to parse through the array.

        Comment


        • #5
          yah I could do that too.. i will give that a go thanks

          Comment


          • #6
            Solution --

            I kept the Mirth code the same,
            but in my stored procedure the code was changed to this:

            Code:
             WHERE reg.Location_MisLocID IN (SELECT value FROM STRING_SPLIT(@RuleBuilderProfileLocations, ','))
            thanks to everyone who had ideas.

            Comment

            Working...
            X