Consultar varios LIKE´s sin SQL Dinámico

--Creamos una tabla temporal para la prueba
Declare @vTable TABLE(id INT, NAME VARCHAR(100))
INSERT INTO @vTable
SELECT 1,'Jose' UNION ALL
SELECT 2,'Gabriel' UNION ALL
SELECT 3,'Gabino' UNION ALL
SELECT 4,'Francisco'

DECLARE @vParam VARCHAR(100)
-- Aqui introducimos los valores separados por coma.
SET @vParam = 'Gab,Fran'

-- Utilizamos CROSS APPLY para completar esta tarea.
SELECT * FROM @vTable
CROSS APPLY (SELECT [item] FROM dbo.fnSplit(@vParam,',')) b
WHERE NAME LIKE '%' + b.[item] + '%'

/*
* La funcion dbo.fnSplit es muy común encontrarla en la
* red, pero se las dejo aqui para que la utilicen
* */

create FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- Lista de los valores separados
  , @sDelimiter VARCHAR(8000) = ',' -- Delimitador
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
    DECLARE @sItem VARCHAR(8000)
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
     BEGIN
         SELECT
          @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
          @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

         IF LEN(@sItem) > 0
          INSERT INTO @List SELECT @sItem
     END

    IF LEN(@sInputList) > 0
         INSERT INTO @List SELECT @sInputList -- Ultimo item
    RETURN
END