Declare @vTable TABLE(id INT, NAME VARCHAR(100))
INSERT INTO @vTable
    SELECT 1,'Shamas Qamar' UNION ALL
    SELECT 2,'Atif' UNION ALL
    SELECT 3,'Kashif' UNION ALL
    SELECT 4,'Imran' 

DECLARE @vParam VARCHAR(100)
-- To check the values with LIKE operator. These are comma separated.
SET @vParam = 'Sha,hif'

-- Used CROSS APPLY to accomplish the task...
SELECT * FROM @vTable 
CROSS APPLY (SELECT [value] FROM dbo.fnSplit(@vParam,',')) b
WHERE NAME LIKE '%' + b.[VALUE] + '%'

http://www.sqlservercentral.com/scripts/SQL+Server+2005/71199/

This uses CROSS APPLY and the fn_split() function to separate out parameters. The @vParam parameter stores your LIKE conditions, separated by commas.
By Atif-ullah Sheikh, 2010/09/16

Way to Check Multiple LIKE without dynamic SQL

SQL