Writing an effective search stored procedure with paging functionality
Writing a search engine can be a nightmare. There are so many permutations of possible search criteria that can be handed down to the DBM system that one is tempted to steer towards writing code that generates dynamic SQL on on the client side. This can become become very difficult to maintain especially since the dynamic SQL generation code will always compile, even if the database structure changes. We do not want our search engine to break down in front of the client. No thank you.
A technique that I have developed is would use a stored procedure to filter out all the ID's of the records that do not satisfy the search critiria and populate a temp table that can be once again filtered for the relevant page that the client is searching for:
The count(*) at the botton of the stored procedure just returns that total no of records so that the caller can determine which page is bieng displayed. Notice how the proc_FetchFilteredID's inserts the filtered IDs into temp table #filtered_ids.
/* fetch the filtered id's */
CREATE
TABLE #filtered_ids (clientid INT)
INSERT
INTO #filtered_ids
EXEC
proc_FetchFilteredIDs @startdate, @enddate, @searchtext, @status
SELECT
id, [name], guid, startTime, finishTime, Status
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY t.pk_id DESC) AS Row,
t
.pk_id as id,
e
.[name],
t
.guid as guid,
t
.startTime as startTime,
t
.finishTime as finishTime,
t
.fk_statusid as Status,FROM tClient t WHERE t.pk_id IN (SELECT jobid FROM #filtered_ids)
)
AS
Entries
WHERE
Row >= @startRowIndex AND Row <= (@startRowIndex + @maximumRows)
/* Get the total number of rows */
SELECT
@totalrowcount = COUNT(*)
from
tClient t,
WHERE
t.pk_id IN (SELECT clientid FROM #filtered_ids)DROP TABLE #filtered_ids
If there are any better ways of writing search stored procedures please shout.
Jean