• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

SQL 2005 RowCount Paging Stored Procedure

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

PROCEDURE [dbo].[sp_TestPage]
                -- Add the parameters for the stored procedure here
                @Where varchar(255),
                @low varchar,
                @high varchar
AS
BEGIN
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
exec(@sql)
END

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!

Many Thanks,
0
AJPatankar
Asked:
AJPatankar
  • 4
  • 3
1 Solution
 
RiteshShahCommented:
I have one small yet efficient paging stored procedure at my blog, have a look at it.

http://www.sqlhub.com/2009/10/paging-stored-procedure-in-sql-server.html
0
 
AJPatankarAuthor Commented:
Thanks Ritesh but I can't quite get that to fit. Your solution accepts paramters for the where clause. However, my application could have anything in the where clause, this needs to be passed in as a string and concatenated in somehow. How would I achieve this with your solutions?

Thanks again,
0
 
RiteshShahCommented:
don't you have fixed fields in WHERE condition. this is something unusual.

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AJPatankarAuthor Commented:
My table has ~100 columns and my application has drop down/checkbox filter criteria so I am using the result of these user interface selections to determine the where clause
filter.jpg
0
 
RiteshShahCommented:
in that case you can generate WHERE clause with below script.


create PROC SearchUser
@PageNum INT, 
@UserPerPage INT, 
@WHERE VARCHAR(MAX)
AS
BEGIN

DECLARE @SQL VARCHAR(MAX)
     set @SQL='Select * FROM
      (
            SELECT
                  UserID,FirstName,LastName,JoiningDate,ROW_NUMBER() Over(Order by UserID) as rowNum
            FROM
                  UsersTable
            WHERE ' + @WHERE +
                  
      ') as t WHERE rowNum BETWEEN (' + CAST(@PageNum-1 AS VARCHAR) + ') * ' + cast(@UserPerPage+1  AS VARCHAR) + ' AND ' + CAST(@PageNum * @UserPerPage  AS VARCHAR)
      print @sql
      --exec (@sql)
END
GO



--now let us call this SP
DECLARE @CONDITION VARCHAR(MAX)
set @CONDITION='userid = 10 '
set @CONDITION=@CONDITION + ' and username=' + CHAR(39) + 'ritesh' + CHAR(39)
EXEC SearchUser 1,2,@CONDITION

Open in new window

0
 
RiteshShahCommented:
your case may help others too so that I post the same script in my article too.

http://www.sqlhub.com/2009/10/paging-stored-procedure-in-sql-server.html
0
 
AJPatankarAuthor Commented:
Absolutely superb. Much better than my original. Many Thanks!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now