September 15, 2010 at 8:37 am
All,
I need to build a query (in fact a stored procedure) with a special behavior.
The matching on certain criteria is mandatory, while the matching on other criteria will give some kind of score.
In order to illustrate this and make it easier to understand, I built the following query (sample):
DECLARE @data_id bigint;
DECLARE @country_id int;
DECLARE @division_id int;
DECLARE @score int;
SET NOCOUNT ON
CREATE TABLE #tmpResults
(
IndexID int IDENTITY (1, 1) NOT NULL,
data_id bigint,
scoreint
)
DECLARE curs_result CURSOR LOCAL FAST_FORWARD
FOR
SELECT data_id, address_country_id, address_division_id
FROM tbl_data
WHERE isActive = 1; -- stupid where clause in the context of this example.
--
-- The here above SELECT statement is based on the Exclusion criteria. Of course, the REAL SQL statement
-- will be much more complex with several JOINS and WHERE conditions.
--
OPEN curs_result;
FETCH NEXT FROM curs_result
INTO @data_id, @country_id, @division_id;
WHILE @@FETCH_STATUS = 0
BEGIN
--
-- Let's now compute the score based on the fact a record matches one or several "optional" criteria
-- For the explanation, the optional criteria are:
-- data_id : between 601 and 10234;
-- country_id : = 1
-- division_id : < 3
--
-- If a "record" matches the 3 criteria, the score will be 3, while if it matches 2 criteria, the score will be 2...
--
SET @score = 0;
IF @data_id BETWEEN 601 AND 10234
BEGIN
SET @score = @score + 1
END
IF @country_id = 1
BEGIN
SET @score = @score + 1
END
IF @division_id < 3
BEGIN
SET @score = @score + 1
END
INSERT INTO #tmpResults(data_id, score ) VALUES (@data_id, @score );
FETCH NEXT FROM curs_result
INTO @data_id, @country_id, @division_id;
END
CLOSE curs_result;
DEALLOCATE curs_result;
--
-- result (will be replaced by a "paging" routine -- see below)
--
SELECT * FROM #tmpResults order by score DESC;
DROP Table #tmpResults
----------------------------
In fact the objective is to be used in a new Web Site. Users will have access to an "extended search" screen which will allow them to define a set of search criteria.
The outcome of the definition of these criteria will be the definition of a "dynamic" SQL which will be passed to a stored procedure, to return paged results.
In other words, the query as described here above will be built dynamically and run using EXEC sp_executeSQL.
The contents of the #tmpResults database will be used in a stored procedure, similar to this one:
CREATE PROCEDURE [dbo].[stp_GenericSearchWithPaging]
(
@in_page_indexint = 0,-- zero based
@in_page_sizeint = 25,
@in_fieldsnvarchar(2000) = '*',
@in_fromjoinnvarchar(2000) = '',
@in_wherenvarchar(2000) = ' 1=1 ',
@in_orderbynvarchar(500) = ''
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlStmtnvarchar(max),
@minRowIdint,
@recctint-- total # of records
-- Compute min row Id
SET @minRowId = @in_page_index * @in_page_size
-- Build and execute SQL statement that counts to total number of records
SET @sqlStmt = 'SELECT @recct = COUNT(*) FROM ' + @in_fromJoin + ' WHERE ' + @in_where
EXEC sp_executeSQL @sqlStmt, @params = N'@recct INT OUTPUT', @recct = @recct OUTPUT
-- Build and execute SQL Statement that returns the records
SET @sqlStmt = 'SELECT TOP ('+CONVERT(varchar(10),@in_page_size)+') * ' +
'FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@in_orderby+') AS RowID,' +
@in_fields + ' FROM ' + @in_fromjoin + ' WHERE ' + @in_where + ' ) TmpTable ' +
'WHERE TmpTable.RowId > ' + CONVERT(varchar(10),@minRowId) + ';'
--PRINT @sqlStmt
EXEC(@sqlStmt)
-- Finally returns the total number of records
SELECT @recct AS TotalRows;
END
-------
So the final question:
Does anyone know a better solution that the one I described in this post?
Considering the fact that the number of concurrent users could be (hopefuly) high and the "tbl_data" could be very large, any hint to improve "my solution" is more than welcome.
Many thanks in advance, for any idea.
Best Regards,
Didier
September 15, 2010 at 9:36 am
I don't about the specifics of your scenario, but in general terms set based is superior to a cursor for this: SELECT score = CASE
WHEN data_id BETWEEN 601 AND 10234 THEN
1
ELSE
0
END
+ CASE
WHEN country_id = 1 THEN
1
ELSE
0
END
+ CASE
WHEN division_id < 3 THEN
1
ELSE
0
END
FROM tbl_data
WHERE isActive = 1
Arguably these criteria should not be in code either, but in a table. http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply