I've ended up with a mixture of what's in the link to aspfaq. I've ranked the search results and put them into a table UDF with an identity column, but I haven't put all the fields in - just their id. Then I use a few parameters to work out how many results are to be returned and which page is required. Then I've joined the required id's back to the table they've come from. Then I put it all in a GetRows array on the client.
It means that I can pull just 10 (as default) records from a search with any number of returned results.
I've put the code below for masochists to pull apart in their own time. It seems to work ok for now. Thanks everyone for your input.
--this proc returns the results of a search, but only the number
--of records requested
--searches the info table by title, blurb and date seperately
-- and gives a relevance score
/*declare @Count int
exec dbo.KeyDoc3SEARCH_usp 'positive', -1, 10, 9, @Count OUTPUT
select @Count*/
ALTER PROC dbo.KeyDoc3SEARCH_usp
(
@searchterm nvarchar(60)
, @mDiff int = -1
, @magnitude int= 10
, @page tinyint = 1
, @Count int OUTPUT
)
--@mDiff is the month difference
--@magnitude is the number of results per page
--@page is the page required
--@Count is the total number of records returned by the search
AS
--turn off count to avoid errors with output variable
SET NOCOUNT ON
--use the magnitude and page number to work out the lower and higher bounds of the records
--asked for - eg magnitude 10 = 10 records per page, page = 2 means @lower = 10 and
--@higher = 20
declare @date1 smalldatetime, @date2 smalldatetime, @higher int, @lower int
set @higher = @magnitude * @page
set @lower = @higher - @magnitude
--select @higher, @lower
IF @mDiff >= 0 OR @mDiff IS NULL
BEGIN
--if empty then set for 1 month back
SET @mDiff = -1
END
set @date1 = DATEADD( m, @mDiff, GETDATE() )
set @date2 = GETDATE()
/* -- some ready made inputs for error checking
declare @searchterm nvarchar(60), @date1 smalldatetime, @mDiff int
set @searchterm = N'camhs'
set @date1 = null */
-- the select statement
select i.[id], i.Title, i.Blurb
, dbo.DateFormat_fn(i.DateCreated, ' ') AS DateCreated
, h.Host AS host, u.Path AS path
, u.ALTcontent
, i2.Score
FROM Info AS i
INNER JOIN
--this udf is a 2d array with 3 columns
--1st col is IDENTITY, 2nd col is the id's from the Info table
--3rd col is the search relevance score eg 3 or 2
--sorted by count DESC, then the IDENTITY is added
dbo.SearchResultsKEYDOC_udf(@searchterm,@date1, @date2)
AS i2
ON i.[id] = i2.[Infoid]
-- getting the urls
INNER JOIN InfoRelURL AS r
ON i.[Id] = r.InfoId
INNER JOIN URL AS u
ON r.URLid = u.[id]
INNER JOIN dbo.urlHost_udf() AS h
ON u.Hostid = h.id
-- this where clause restricts the returned records using values derived from @magnitude
-- and @page
WHERE i2.id BETWEEN @lower AND @higher
-- output the count
SELECT @Count = Count(*)
FROM dbo.SearchResultsKEYDOC_udf(@searchterm,@date1, @date2)
SET NOCOUNT OFF
this is the udf that does the search scoring
--last updated 1 9 05 17.20
--select * from dbo.SearchResults_udf('positive', '10 Aug 2005', '1 Sep 2005')
--optimised for KEYDOCs
-- UNION ALL is used to allow a GROUP BY which can then be counted
--giving a rudimentary relevance score
CREATE FUNCTION dbo.SearchResultsKEYDOC_udf
(
@searchterm nvarchar(60)
, @date1 smalldatetime = null
, @date2 smalldatetime = null
)
RETURNS @myTable TABLE
(
id int IDENTITY(1,1)
, Infoid int
, Score tinyint
)
AS
BEGIN
-- the insert
INSERT @myTable
select i.[id]
, COUNT(*) AS Score
FROM Info AS i
INNER JOIN
(
--search the title
select * from dbo.InfoSELECTidsfromTitleKEYDOC_udf(@searchterm)
UNION ALL
--search the blurb
select * from dbo.InfoSELECTidsfromBlurbKEYDOC_udf(@searchterm)
UNION ALL
--search between dates
select * from dbo.InfoSELECTidsfromDatesKEYDOC_udf(@date1, @date2)
) AS i2
ON i2.[id] = i.[id]
-- group by and sort by...
GROUP BY i.[id]
ORDER BY Score DESC
-- return the array
RETURN
END
