>>I created a user function to handle this.
>>
>>

>>CREATE FUNCTION [dbo].[isIN] 
>>(
>>	@lcStr varchar(100) ,
>>	@lcList varchar(max)
>>)
>>RETURNS int 
>>AS
>>BEGIN
>>
>>	DECLARE @Test int = 0
>>
>>	if @lcStr = '' or @lcList=''
>>		set @test=1 
>>	else 
>>	if NOT (@lcStr is null or @lcList is null)
>>	begin 
>>		declare @aa int 
>>		set @aa = charindex(','+@lcStr+',',','+@lcList+',')
>>		if @aa>0
>>			set @Test = 1
>>		else 
>>			set @test = 0
>>	end 
>>
>>	RETURN @Test
>>
>>END
>>GO
>>

>>
>>then, in the conditional clause I would
>>
>>

>>WHERE dbo.isIN([awb_manifestnumber], @awb_manifestnumber)>0
>>

>
>This is very bad as you're not using index this way and making query slow by adding scalar UDF into WHERE. I advise you to never use such approach.

--@awb_manifestnumber is list of ID: ',1,2,3,4,5,'
WHERE CHARINDEX(','+[awb_manifestnumber]+',', @awb_manifestnumber)>0

DROP FUNCTION dbo.SDSplitString
GO
CREATE FUNCTION dbo.SDSplitString(@lcSTRING nvarchar(4000), @lcSEP VARCHAR(10)    )
RETURNS TABLE
AS
--http://stackoverflow.com/questions/2647/split-string-in-sql
 RETURN (WITH tokens(ORD, FPOS, TPOS, LSEP) AS
          (
          SELECT 1, 1, CASE WHEN CHARINDEX(@lcSEP, @lcSTRING)-1<0 THEN LEN('x'+@lcString+'x')-2 ELSE CHARINDEX(@lcSEP, @lcSTRING)-1 END, LEN('x'+@lcSEP+'x')-2
          UNION ALL
          SELECT ORD + 1, TPOS + LSEP+1, CASE WHEN CHARINDEX(@lcSEP, @lcSTRING, TPOS + LSEP+1)=0 THEN LEN('x'+@lcString+'x')-2 ELSE CHARINDEX(@lcSEP, @lcSTRING, TPOS + LSEP+1)-1 END, LSEP
            FROM tokens
            WHERE TPOS <LEN('x'+@lcString+'x')-2
          )
         SELECT FPOS, CASE WHEN TPOS<FPOS THEN FPOS ELSE TPOS END AS TPOS, ORD , SUBSTRING( @lcSTRING, FPOS,TPOS-FPOS+1) AS FIELD, TPOS-FPOS+1 AS LFIELD
          FROM tokens
        )
GO

--@awb_manifestnumber is list of ID: '1,2,3,4,5'
WHERE [awb_manifestnumber] IN (SELECT CAST(FIELD AS INT) FROM dbo.SDSplitString(@awb_manifestnumber,','))

--@awb_manifestnumber is list of ID: '1,2,3,4,5'

CREATE FUNCTION [dbo].[ListToTable](@lcSTRING nvarchar(4000), @lcSEP VARCHAR(10) )
RETURNS  @loList TABLE (ID BIGINT NOT NULL)
AS
BEGIN
 DECLARE @loXML XML
 SET @loXML='<col><e>'+REPLACE(@lcString,@lcSEP,'</e></col><col><e>')+'</e></col>'

 INSERT INTO @loList
 SELECT Tbl.Col.value('e[1]', 'bigint') AS LID
   FROM @loxml.nodes('//col') Tbl(Col)
 RETURN
END --ListToTable
GO

WHERE [awb_manifestnumber] IN (SELECT ID FROM dbo.ListToTable(@awb_manifestnumber,','))