?
Solved

SQL 2005 RowCount Paging Stored Procedure

Posted on 2010-01-12
7
Medium Priority
?
529 Views
Last Modified: 2012-05-08
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
Comment
Question by:AJPatankar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 26291549
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
 

Author Comment

by:AJPatankar
ID: 26291745
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 26291800
don't you have fixed fields in WHERE condition. this is something unusual.

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:AJPatankar
ID: 26291835
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
 
LVL 31

Accepted Solution

by:
RiteshShah earned 2000 total points
ID: 26291847
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 26291855
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
 

Author Comment

by:AJPatankar
ID: 26291945
Absolutely superb. Much better than my original. Many Thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question