SQL Server comma-delimited string parameter

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!

Answer 1

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

Answer 2

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.

Answer 3

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?

Answer 4

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.