Join column in where clause using split

Question

My stored procedure accepts a parameter with values like

Declare @Temp VarChar(Max)
Set @Temp = 'S1CW3733|1050105000224,S1CW4923|1050105000009'

Where values is like

Column1|Column2,Column1|Column2,Column1|Column2,

I want to apply same in SQL query like

Select * 
From ATMStatus 
Where ATM + '|' + Fault IN (@Temp)

How can I achieve this?

Database is SQL Server 2008

Duplicates : http://stackoverflow.com/questions/10581772/sql-server-2008-how-to-split-a-comma-seperated-value-to-columns , http://stackoverflow.com/questions/8215267/how-to-split-the-comma-separated-string-with-out-using-loop-in-stored-procedure — Stuart Blackler, Nov 16 '12 at 10:47

score 2 · Accepted Answer · answered Nov 16 '12 at 10:48

For small tables, you can use the table scanning solution, assuming ATM and Fault can never contain comma (,) or pipe (|):

Select *
  From ATMStatus
 Where ',' + @Temp + ',' LIKE '%,' + ATM + '|' + Fault + ',%';

For large tables, you'll need to employ a splitting function to turn the variable into a multi-row, 2 column table, which would then be used something like:

Select a.*
  From ATMStatus a
  join dbo.SplitVarTo2ColumnTable(@Temp) b
       on b.Column1 = a.ATM and b.Column2 = a.Fault;

Join column in where clause using split

1 Answers1