CREATE PROCEDURE get_SearchPaging
	(
	@array varchar(1000),   --search_by
	@separator char(1),
	@page int,            --page number
	@recsPerPage int,     --records per page
	@search_in int        --category to search in
	)

 AS

set nocount on

declare @separator_position int
declare @array_value varchar(1000)
declare @like_text varchar(1000)

Create table #SearchResults 
(
prod_id int
)

set @array=@array + @separator

While patindex('%' + @separator + '%' , @array) <> 0
begin
	select @separator_position =  patindex('%' + @separator + '%' , @array) 
	select @array_value = left(@array, @separator_position-1)
	select @like_text = '%' + @array_value + '%'

	if @search_in=1000    --if search_in=all categories 
	begin
		insert into #SearchResults
		select prod_id
		from products
		where prod_num like @like_text
		
		insert into #SearchResults
		select prod_id
		from products 
		where prod_name like @like_text
		
		insert into #SearchResults
		select prod_id
		from products 
		where prod_short_desc like @like_text
	end
	else   
--otherwise search in the categories that are associated with 
--the supercategory
	begin
		insert into #SearchResults
		select a.prod_id
		from products a, productsXgroup b, groupXsupergroup c
		where a.prod_num like @like_text
		and c.supergroup_id=@search_in
		and c.group_id=b.group_id
		and b.prod_id=a.prod_id

		insert into #SearchResults
		select a.prod_id
		from products a, productsXgroup b, groupXsupergroup c
		where a.prod_name like @like_text
		and c.supergroup_id=@search_in
		and c.group_id=b.group_id
		and b.prod_id=a.prod_id

		insert into #SearchResults
		select a.prod_id
		from products a, productsXgroup b, groupXsupergroup c
		where a.prod_short_desc like @like_text
		and c.supergroup_id=@search_in
		and c.group_id=b.group_id
		and b.prod_id=a.prod_id
	end
	select @array=stuff(@array,1,@separator_position, '')
end

--create a table to rank the records added to #searchResults
Create table #Rank (temp_id int IDENTITY,prod_id int, Rank int)
Insert into #Rank
select prod_id, rank=count(*)
from #SearchResults
group by prod_id
order by 2 desc

--get my first and last record for each page
Declare @FirstRec int, @LastRec int  
select @FirstRec=(@page - 1) * @RecsPerPage  
select @LastRec=(@page * @RecsPerPage + 1)  

--select out records per page starting with best rank
select  a.prod_id, b.prod_num,  b.prod_name, b.prod_short_desc, b.prod_mixName,
totalRecords=(select count(*) from #SearchResults) , c.rank
from #SearchResults  a, vProducts b, #Rank c
where c.temp_id>@firstRec and c.temp_id<@lastRec  
	and a.prod_id=b.prod_id
	and a.prod_id=c.prod_id
group by a.prod_id, b.prod_num,  b.prod_name, b.prod_short_desc, b.prod_mixName, c.rank
order by 7 desc

set nocount off