Dynamic WHERE from delimited list param

 

@Customers

varchar(8000) = null

Declare @Delimiter varchar(2)
Declare @Check nvarchar(1)
Declare @Pos int
Declare @Cnt int
Declare @NextPos int
Declare @NextString varchar(8000)
Declare @Filter varchar(8000)

SET @Delimiter='|'

--Parse Customer Parameter
--Build Statement - CustomerCode = 'C1' OR CustomerCode='C2' ...
IF NOT @Customers IS NULL
BEGIN
SET @Check = right(@Customers,1)
 
 --Check for trailing Comma, if not exists, INSERT
 if (@Check <> @Delimiter )
 SET @Customers = @Customers + @Delimiter

 SET @Pos = charindex(@Delimiter,@Customers)
 SET @NextPos = 1
 SET @Cnt = 0

 --Loop while there is still a delim in the String
 while (@Pos <>  0) 
 begin
 SET @NextString = substring(@Customers,1,@Pos - 1)

 IF @Cnt > 0 And len(@NextString)>0
  SELECT @CustomerSQL = @CustomerSQL + ' OR ' + 'CompanyCode=''' + @NextString + ''''
 ELSE IF len(@NextString)>0
  SELECT @CustomerSQL = 'CompanyCode=''' + @NextString  + ''''
  
 SET @Customers = substring(@Customers,@pos +1,len(@Customers))
 
 SET @Cnt =  @Cnt + 1
 SET @NextPos = @Pos
 SET @Pos  = charindex(@Delimiter,@Customers)
 end
END