Answered by:
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) aselect 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
3438228214Second query results:
3438233601 3438233601 2021-01-04 2020
3438233601 3438233601 2021-01-04 2020Third 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 2020Thanks 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