locked
SQL Server comma-delimited string parameter RRS feed

  • Question

  • User1211213365 posted

    Can someone please help me figure out what I'm doing wrong?  I am trying to pass a simple delimited string to a stored proc and use it in a where-clause.  I am currently putting it into a temp table and using IN.  I inserted 4 rows into that table and expect all matching values from my large table to be returned.  If I run the following code, I get the 4 rows I inserted into the temp table, then only two that match from the IN clause, yet I get six matching rows from a specific where string.  Why am I not getting six rows from the IN query?  They only match on the first string from the split, whichever order I set them in.

     declare @clm_ids varchar(4000)
     set @clm_ids = N'3438233601, 3438233502, 3438228214, 3438228214'

     select value into #tempClaims from string_split(@clm_ids, ',')

     select value from #tempClaims

     select value, clm_id, INSERT_DATE, year from d_837_bypass b
     cross apply (select value from #tempClaims t where t.value = b.clm_id) a

     select clm_id, INSERT_DATE, year from d_837_bypass b
     where b.clm_id in ('3438233601', '3438233502', '3438228214', '3438228214')

     drop table #tempClaims

    First query results:

    3438233601
     3438233502
     3438228214
     3438228214

    Second query results:

    3438233601 3438233601 2021-01-04 2020
    3438233601 3438233601 2021-01-04 2020

    Third query results:

    3438228214 2021-01-04 2020
    3438228214 2021-01-04 2020
    3438233502 2021-01-04 2020
    3438233502 2021-01-04 2020
    3438233601 2021-01-04 2020
    3438233601 2021-01-04 2020

    Thanks in advance for any insight!

    Tuesday, March 16, 2021 9:47 PM

Answers

  • User753101303 posted

    Hi,

    More likely because of the extra space you added before each separator?  What if you delete this extra space or if you are using LTRIM on the pocessed value?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 17, 2021 2:29 PM

All replies

  • User1535942433 posted

    Hi puzzledPete,

    What is your table d_837_bypass data?

    As far as I think,you need to learn more about cross apply and in.

    Cross apply: It's same with INNER JOIN to join the results from two tables.

    IN:The IN operator allows you to specify multiple values in a WHERE clause.

    More details,you could refer to below these articles:

    https://www.sqlshack.com/the-difference-between-cross-apply-and-outer-apply-in-sql-server/

    https://www.w3schools.com/sql/sql_in.asp

    Best regards,

    Yijing Sun

    Wednesday, March 17, 2021 5:41 AM
  • User1211213365 posted

    d_837_bypass table has a clm_id, which should join to the value in the #tempClaims table.  There is only one column in the temp table.  My goal is to get all of the columns in d_837_bypass that has a clm_id that exists in the tempTable.  It only wants to match on the first value in #tempClaims, so I tried the CROSS APPLY to be thorough.  I have tried subqueries and inner joins to that tempTable.  I am not married to cross apply, just trying to find what works.

    Wednesday, March 17, 2021 1:40 PM
  • User753101303 posted

    Hi,

    More likely because of the extra space you added before each separator?  What if you delete this extra space or if you are using LTRIM on the pocessed value?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 17, 2021 2:29 PM
  • User1211213365 posted

    omg...you have GOT to be kidding me.  That was it.  I never would have even thought to look at that.  Thank you soooooo much.  It's working now.

    Wednesday, March 17, 2021 6:31 PM