0

i am tying to get record by passing string comma separated ids. My query in where is like,

Charindex(cast(v.Status as varchar(8000)), @status)

let suppose i pass parameter @status=15. now it will show me all records whose id is "1", "5"and "15" ? I just need record having id = 15 Hopes for your suggestions thanks

EDIT:

I actually use this link for solution,

RBarry Young answer

Passing a varchar full of comma delimited values to a SQL Server IN function

Doc
  • 179
  • 1
  • 18
  • I recommend again that answer. Use a String Splitter (like `STRING_SPLIT`, and XML Splitter or a tally table splitter), or use a Table-Type parameter. Never use a `WHILE` to split a delimited list; they perform awfully. There are 100's of examples on how to do this on Stack Overflow. – Thom A May 21 '19 at 10:32

1 Answers1

0

Using CSV data in this way is suboptimal, but here is one trick you may try using LIKE:

WHERE ',' + CAST(v.Status as varchar(8000)) + ',' LIKE '%,' + @status + ',%'

The trick works because now, for example, we are comparing ,1,5,15, against just ,15, so there is no chance for a mismatch.

I will go on to say that this is really bad table/database design. You should ideally just be using a WHERE IN clause with a list of values. Or, better yet, get these CSV values normalized and into a separate table.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • yes you are right it is not a good approch but due to lack of time i am usinf this solution. i tried this solution it works thanks. – Doc May 21 '19 at 11:20