SQL 2005 RowCount Paging Stored Procedure
Posted on 2010-01-12
How would I make the following SQL 2005 code into an efficient stored procedure:
WITH TestRows AS
SELECT *, ROW_NUMBER() OVER(ORDER BY ID) AS 'RowNum' FROM TEST %s
SELECT * FROM TestRows WHERE RowNum BETWEEN %d AND %d
%s is replaced by a where clause defined at runtime. E.g. %s = WHERE ID = 1 or %s = "WHERE User = 'bob' AND Result='Pass'"
%d is replaced by numbers defining the rows to turn, e.g. 0 and 499 or 500 and 999 respectively
I did the following which works
-- Add the parameters for the stored procedure here
DECLARE @sql varchar(1000)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
set @sql = 'WITH TestRows AS ( SELECT *, ROW_NUMBER() OVER(ORDER BY ID) AS RowNum FROM TEST ' + @Where + ')
SELECT * FROM TestRows WHERE RowNum BETWEEN ' + @low + ' AND ' + @high
But as I am effectively creating the sql dynamically in the stored procedure (as the WHERE clause is NEVER known at until runtime) it actually runs marginally slower than sending over the raw sql!