Paging stored procedures, no wrong version, I am pro-test

--drop procedure P_page
--go

CREATE PROCEDURE P_page

(
@Tables varchar (1000)--table name such as TestTable
@PrimaryKey varchar,--table's primary key, must be unique
@Sort varchar = NULL,--sort fields such as f_name ASC or F_name desc (Note that you can only have one sort field)
@CurrentPage int = 1,--Current page
@PageSize int =,---per page size
List of fields displayed @Fields varchar (1000) = ' * ',--
@Filter varchar (1000) = NULL,--conditional statement, without where, such as F_id>3
@Group varchar (1000) = NULL,--grouping field
@TotalPage int output--Total number of pages returned
)

With Encryption---encrypted storage

As



SET NOCOUNT on
Declare @intResult Int

Begin Tran

DECLARE @sql nvarchar (4000)

If @Filter is null or @Filter = '
Set @Sql = ' Select @intResult = count (' + @PrimaryKey + ') from ' + @Tables
Else
Set @Sql = ' Select @intResult = count (' + @PrimaryKey + ') from ' + @Tables + ' where + ' + @Filter

EXEC sp_executesql @sql, N ' @intResult int OUTPUT ', @intResult output--total number of records calculated
Select @TotalPage =ceiling ((@intResult +0.0)/@PageSize)--Calculate total pages


IF @Sort is NULL or @Sort = '
SET @Sort = @PrimaryKey

DECLARE @SortTable varchar (100)
DECLARE @SortName varchar (100)
DECLARE @strSortColumn varchar (200)
DECLARE @operator char (2)
DECLARE @type varchar (100)
DECLARE @prec int

IF CHARINDEX (' DESC ', @Sort) >0
BEGIN
SET @strSortColumn = REPLACE (@Sort, ' DESC ', ')
SET @operator = ' <= '
End
ELSE
IF CHARINDEX (' ASC ', @Sort) > 0
BEGIN
SET @strSortColumn = REPLACE (@Sort, ' ASC ', ')
SET @operator = ' >= '
End

ELSE
BEGIN
SET @strSortColumn = @SORT
SET @operator = ' >= '
End

IF CHARINDEX ('. ', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING (@strSortColumn, 0, CHARINDEX ('. ', @strSortColumn))
SET @SortName = SUBSTRING (@strSortColumn, CHARINDEX ('. ', @strSortColumn) + 1, LEN (@strSortColumn))
End
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
End

Select @type =t.name, @prec =c.prec
From sysobjects o
JOIN syscolumns C on o.id=c.id
JOIN systypes T on C.xusertype=t.xusertype
Where o.name = @SortTable and c.name = @SortName

IF CHARINDEX (' char ', @type) > 0
SET @type = @type + ' (' + CAST (@prec as varchar) + ') '

DECLARE @strPageSize varchar (50)
DECLARE @strStartRow varchar (50)
DECLARE @strFilter varchar (1000)
DECLARE @strSimpleFilter varchar (1000)
DECLARE @strGroup varchar (1000)

IF @CurrentPage < 1
SET @CurrentPage = 1

SET @strPageSize = CAST (@PageSize as varchar (50))
SET @strStartRow = CAST ((@CurrentPage-1) * @PageSize + 1) as varchar (50))

IF @Filter is not NULL and @Filter!= '
BEGIN
SET @strFilter = ' Where ' + @Filter + '
SET @strSimpleFilter = ' and ' + @Filter + '
End
ELSE
BEGIN
SET @strSimpleFilter = '
SET @strFilter = '
End

IF @Group is not NULL and @Group!= '
SET @strGroup = ' GROUP by ' + @Group + '
ELSE
SET @strGroup = '

Set @sql = ' DECLARE @SortColumn ' + @type + '
SET RowCount ' + @strStartRow + '
Select @SortColumn = ' + @strSortColumn + ' from ' + @Tables + @strFilter + ' + @strGroup + ' ORDER BY ' + @Sort + '
SET RowCount ' + @strPageSize + '
Select ' + @Fields + ' from ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' + @strGroup + ' ORDER BY ' + @Sort + '
'

EXEC (@sql)
--print @sql

-------------------------------------------------------------------------------------------------

If @ @Error <> 0
Begin
RollBack Tran
Return-1
End
Else
Begin
Commit Tran
Return @intResult---Total number of records returned
End

Go

－－－－－－
Trial:
declare @TotalPage int, @RecordCount int
Set @TotalPage =1
Execute @RecordCount =dbo.p_page ' Jxcliushui ', ' id ', ' date desc ', 500, 15, ' * ', ' date >= ' 2007-10-01 ', ', ', @TotalPage Output
Print @TotalPage
Print @RecordCount
－－－－－
Return Result: 1308
                    19611