September 16, 2010 at 6:36 am
Dear All,
For the website I am developing, I needed a "generic" stored procedure to return search results with paging functionality.
Please find here after the stored procedure.
Some explanation:
1. I need to create the SQL statement dynamically, based on the criteria (and scope) defined by the users. This explains why I need to pass the various parameters to the stored procedure
2. To explain the parameters, please consider the following "pseudo" way of building the SELECT statement:
@sqlStmt = 'SELECT ' + @in_fields + ' FROM ' + @in_fromjoin + ' WHERE ' + @in_where + ' ORDER BY ' + @in_orderby + ';'
Now the question:
As you will see in the code of the stored procedure, I am running 2 SQL statements:
1. The first one, to count to total number of records that match the criteria => result is stored in @recct (this is used to compute the number of pages)
2. The second one, to return a part of the result set, limited to the page I need to display.
The question is:
Am I obliged to have 2 distinct queries to return the total number of available records or could I "try" to find out a way of running only 1 query which would return both pieces of information (count and top ...)?
Stored procedure:
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
In advance, many thanks for your help
Didier
September 16, 2010 at 6:40 am
Hi ,
IMO , this is wrong , you dont want to do this. Generic Procedure are rarely a good idea.
For paging , try this link http://www.sqlservercentral.com/articles/paging/69892/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply