September 14, 2010 at 10:57 am
Hi,
Come across two elegant solutions which combined will give me paged search results. One using the ROWNUMBER() function to select blocks for paging in a web application, and an inner select for search results on a multi-word search term.
I'm nearly there in combining the two, but although I get my result set, I can't get a count on it as well, and I need this for the pagination navigation.
It seems as though I can only select on table (alias) 'jobEntries' once, either for a result set or for a count (@jobCount), but not for both.
I guess this is a limitation of the 'WITH AS' construct?
Please help!
ALTER procedure SearchJobs
(
@SearchString varchar(200),
@MatchType INT = 0,
@pageIndex INT,
@pageSize INT,
@jobCount INT OUTPUT
)
AS
-- SeachString: String of 1 or more search terms, all separated by spaces
-- MatchType: 0=match any, 1 =match all, 2 = exact match of entire expression only
-- Results are returned in order of relevance
declare @i1 int;
declare @i2 int;
declare @Word varchar(100);
declare @Words table (Word varchar(100) not null);
declare @WordCount as integer;
set nocount on
-- Parse the SearchString to extract all words:
if (@MatchType != 2)
begin
set @SearchString = ' ' + @SearchString + ' ';
set @i1 = 1;
while (@i1 != 0)
begin
set @i2=charindex(' ', @SearchString, @i1+1)
if (@i2 != 0)
begin
set @Word = rtrim(ltrim(substring(@SearchString, @i1+1, @i2-@i1)))
if @Word != '' insert into @Words select @Word
end
set @i1 = @i2
end
end
else
insert into @Words select ltrim(rtrim(@SearchString))
-- Get the total # of words:
set @WordCount = (select count(*) from @Words)
-- Return Results in order of relevance:
-- get total jobs
DECLARE @startRowIndex INT;
SET @startRowIndex = (@pageIndex * @pageSize) +1;
WITH jobEntries AS
(
SELECT a.MatchPct, T.jobId, T.jobTitle, T.jobDescription, T.jobemploymentSectorId, ROW_NUMBER() OVER (ORDER BY a.MatchPct) AS 'RowNumber'
from
jobs T
inner join
(
select
jobId, Count(*) * 1.0 / @WordCount as MatchPct
from
jobs T
inner join
@Words W on ' ' + T.jobTitle + ' ' + T.jobDescription + ' ' like '%[^a-z]' + Word + '[^a-z]%'
group by
jobId
) a on T.jobId = a.jobId
where
MatchPct = 1 or @MatchType <>1
)
SELECT TOP 100 *
FROM jobEntries
WHERE RowNumber between @startRowIndex and @startRowIndex + @pageSize - 1
SELECT @jobCount = (SELECT COUNT(*) FROM jobEntries) -- Fails
September 14, 2010 at 11:27 am
pixelene (9/14/2010)
Hi,Come across two elegant solutions which combined will give me paged search results. One using the ROWNUMBER() function to select blocks for paging in a web application, and an inner select for search results on a multi-word search term.
I'm nearly there in combining the two, but although I get my result set, I can't get a count on it as well, and I need this for the pagination navigation.
It seems as though I can only select on table (alias) 'jobEntries' once, either for a result set or for a count (@jobCount), but not for both.
I guess this is a limitation of the 'WITH AS' construct?
Please help!
Nope, not a limitation. It's only available for THE NEXT select/insert/update/delete statement. The one that it's failing on is the second statement.
You might want to look at selecting the results into a temp table, then run both selects off of it.
FYI, you might want to check out this post for a much better way to strip out your words: Click here for the latest Delimited Split Function.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 14, 2010 at 11:37 am
WayneS (9/14/2010)
Nope, not a limitation. It's only available for THE NEXT select/insert/update/delete statement. The one that it's failing on is the second statement.You might want to look at selecting the results into a temp table, then run both selects off of it.
FYI, you might want to check out this post for a much better way to strip out your words: Click here for the latest Delimited Split Function.
Or, instead of a temp table, just change the select to another CTE, so you have something like:
;WITH CTE1 AS (stuff, blah)
,CTE2 AS (Stuff, Joins on CTE1, Etc)
SELECT (Whatever from CTE1, CTE2)
That is how you get around the limitation of only being able to use a CTE on the next select statement. Sorry for the pseudo-code 🙂
September 14, 2010 at 1:04 pm
Thanks for your responses.
Yes I had considered this further CTE approach, but wouldn't I have to run the nasty search select twice, just to get a count? Looks like the temp table is the only route if I want to keep it as efficient as possible ...
September 14, 2010 at 1:19 pm
Yep, two hits against a temp table (especially with an index) can be cheaper than two hits against a nasty CTE.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply