Cross join Vs Loop in SQL

 Search different keywords in sql database through Cross join while using loop
 First run Create function code then execute the database query

// Create Function First
Create function [dbo].[fn_split](
@str varchar(max),
@delimiter char(1)
)
returns @returnTable table (idx int primary key identity, item varchar(8000))
as
begin
declare @pos int
select @str = @str + @delimiter

while len(@str) > 0
    begin
        select @pos = charindex(@delimiter,@str)
        if @pos = 1
            insert @returnTable (item)
                values (null)
        else
            insert @returnTable (item)
                values (substring(@str, 1, @pos-1))
        select @str = substring(@str, @pos+1, len(@str)-@pos)
      
    end
return
end



// Query part

DROP TABLE vTable3
DROP TABLE vTable1

CREATE TABLE vTable1 (id1 INT, NAME VARCHAR(100)Primary Key (id1))
CREATE TABLE vTable3 (id3 INT, id1 int, Deductions VARCHAR(100),Primary Key (id3),
Foreign Key (id1) references vTable1(id1))


INSERT INTO vTable1
    SELECT 1,'Shamas' union all
    select 5,'Qamar' UNION ALL
    SELECT 2,'Atif' UNION ALL
    SELECT 3,'Kashif' UNION ALL
    SELECT 4,'Imran'

INSERT INTO vTable3
    SELECT 1,2,'70' UNION ALL
    SELECT 2,5,'80'


DECLARE @vParam VARCHAR(100)
SET @vParam = '70,s'

SELECT distinct t1.*,t3.* FROM vTable1 t1
left Outer join vTable3 t3 on t3.id1=t1.id1
CROSS APPLY (SELECT item FROM dbo.[fn_split](@vParam,',')) b
WHERE
(t1.NAME LIKE '%' + b.item + '%'
  or t3.Deductions LIKE '%' + b.item + '%'
)