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 + '%'
)
