CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Shaggy
Shaggy

Reputation: 5800

Join column in where clause using split

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

Upvotes: 1

Views: 241

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

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;

Upvotes: 2

Related Questions