nick
3/21/2007 10:10:00 PM
Hi I came across this example that returns a result set with arbitrary
start and end rows and was wondering if anyone could help me modify it
so that it'll take an arbitrary column as for ordering. The SP was
created fine but when I try to run it with say get_users 10, 20,
'username' it gives me an error:
---------------------
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@tempcol'.
----------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[get_users]
@pagenum INT = 1,
@perpage INT = 10,
@ordercolumn VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ubound INT,
@lbound INT,
@pages INT,
@rows INT
SELECT
@rows = COUNT(*),
@pages = COUNT(*) / @perpage
FROM
Users WITH (NOLOCK)
IF @rows % @perpage != 0 SET @pages = @pages + 1
IF @pagenum < 1 SET @pagenum = 1
IF @pagenum > @pages SET @pagenum = @pages
SET @ubound = @perpage * @pagenum
SET @lbound = @ubound - (@perpage - 1)
SELECT CurrentPage = @pagenum, TotalPages = @pages, TotalRows =
@rows
-- this method determines the string values
-- for the first desired row, then sets the
-- rowcount to get it, plus the next n rows
DECLARE @tempcol VARCHAR(50), @sql VARCHAR(1000)
SET ROWCOUNT @lbound
set @sql = 'SELECT ' + @ordercolumn + ' AS @tempcol FROM Users WITH
(NOLOCK) '
set @sql = @sql + ' ORDER BY ' + @ordercolumn
exec(@sql)
SET ROWCOUNT @perPage
set @sql = 'SELECT ' + @ordercolumn + ' FROM Users WITH (NOLOCK)
WHERE ' + @ordercolumn + ' >= ' + @tempcol
set @sql = @sql + ' ORDER BY ' + @ordercolumn
exec(@sql)
SET ROWCOUNT 0
END