【澳门金沙vip】sqlserver 存储过程分页代码第1/2页

复制代码 代码如下:declare @TotalCount int
declare @TotalPageCount int exec P_viewPage_A ‘type1′,’*’,’id’,”,’id
asc’,1,0,4,3,@TotalCount output,@TotalPageCount output select * from
type1 Create PROC P_viewPage_A /* nzperfect [no_mIss]
高效通用分页存储过程(双向检索) 2007.5.7 QQ:34813284
敬告:适用于单一主键或存在唯一值列的表或视图
ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围 */
@TableName VARCHAR(200), –表名 @FieldList VARCHAR(2000),
–显示列名,如果是全部字段则为* @PrimaryKey VARCHAR(100),
–单一主键或唯一值键 @Where VARCHAR(2000), –查询条件
不含’where’字符,如id10 and len(userid)9 @Order VARCHAR(1000), –排序
不含’order by’字符,如id asc,userid desc,必须指定asc或desc
–注意当@SortType=3时生效,记住一定要在最后加上主键,否则会让你比较郁闷
@SortType INT, –排序规则 1:正序asc 2:倒序desc 3:多列排序方法
@RecorderCount INT, –记录总数 0:会返回总记录 @PageSize INT,
–每页输出的记录数 @PageIndex INT, –当前页数 @TotalCount INT OUTPUT,
–记返回总记录 @TotalPageCount INT OUTPUT –返回总页数 AS SET NOCOUNT ON
IF ISNULL(@TotalCount,”) = ” SET @TotalCount = 0 SET @Order =
RTRIM(LTRIM(@Order)) SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey)) SET
@FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),’ ‘,”) WHILE CHARINDEX(‘,
‘,@Order) 0 OR CHARINDEX(‘ ,’,@Order) 0 BEGIN SET @Order =
REPLACE(@Order,’, ‘,’,’) SET @Order = REPLACE(@Order,’ ,’,’,’) END IF
ISNULL(@TableName,”) = ” OR ISNULL(@FieldList,”) = ” OR
ISNULL(@PrimaryKey,”) = ” OR @SortType 1 OR @SortType 3 OR
@RecorderCount 0 OR @PageSize 0 OR @PageIndex 0 BEGIN PRINT(‘ERR_00′)
RETURN END IF @SortType = 3 BEGIN IF (UPPER(RIGHT(@Order,4))!=’ ASC’ AND
UPPER(RIGHT(@Order,5))!=’ DESC’) BEGIN PRINT(‘ERR_02’) RETURN END END
DECLARE @new_where1 VARCHAR(1000) DECLARE @new_where2 VARCHAR(1000)
DECLARE @new_order1 VARCHAR(1000) DECLARE @new_order2 VARCHAR(1000)
DECLARE @new_order3 VARCHAR(1000) DECLARE @Sql VARCHAR(8000) DECLARE
@SqlCount NVARCHAR(4000) IF ISNULL(@where,”) = ” BEGIN SET
@new_where1 = ‘ ‘ SET @new_where2 = ‘ WHERE ‘ END ELSE BEGIN SET
@new_where1 = ‘ WHERE ‘ + @where SET @new_where2 = ‘ WHERE ‘ + @where

文章找到了两篇关于mssql
server存储过程的高效分页代码,有需要的朋友可以参考一下。
代码如下复制代码

  • ‘ AND ‘ END IF ISNULL(@order,”) = ” OR @SortType = 1 OR @SortType =
    2 BEGIN IF @SortType = 1 BEGIN SET @new_order1 = ‘ ORDER BY ‘ +
    @PrimaryKey + ‘ ASC’ SET @new_order2 = ‘ ORDER BY ‘ + @PrimaryKey + ‘
    DESC’ END IF @SortType = 2 BEGIN SET @new_order1 = ‘ ORDER BY ‘ +
    @PrimaryKey + ‘ DESC’ SET @new_order2 = ‘ ORDER BY ‘ + @PrimaryKey + ‘
    ASC’ END END ELSE BEGIN SET @new_order1 = ‘ ORDER BY ‘ + @Order END IF
    @SortType = 3 AND CHARINDEX(‘,’+@PrimaryKey+’ ‘,’,’+@Order)0 BEGIN SET
    @new_order1 = ‘ ORDER BY ‘ + @Order SET @new_order2 = @Order + ‘,’ SET
    @new_order2 =
    REPLACE(REPLACE(@new_order2,’ASC,’,'{ASC},’),’DESC,’,'{DESC},’) SET
    @new_order2 =
    REPLACE(REPLACE(@new_order2,'{ASC},’,’DESC,’),'{DESC},’,’ASC,’) SET
    @new_order2 = ‘ ORDER BY ‘ +
    SUBSTRING(@new_order2,1,LEN(@new_order2)-1) IF @FieldList ‘*’ BEGIN
    SET @new_order3 = REPLACE(REPLACE(@Order + ‘,’,’ASC,’,’,’),’DESC,’,’,’)
    SET @FieldList = ‘,’ + @FieldList WHILE CHARINDEX(‘,’,@new_order3)0
    BEGIN IF
    CHARINDEX(SUBSTRING(‘,’+@new_order3,1,CHARINDEX(‘,’,@new_order3)),’,’+@FieldList+’,’)0
    BEGIN SET @FieldList = @FieldList + ‘,’ +
    SUBSTRING(@new_order3,1,CHARINDEX(‘,’,@new_order3)) END SET
    @new_order3 =
    SUBSTRING(@new_order3,CHARINDEX(‘,’,@new_order3)+1,LEN(@new_order3))
    END SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList)) END END SET
    @SqlCount = ‘SELECT
    @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/’ +
    CAST(@PageSize AS VARCHAR)+’) FROM ‘ + @TableName + @new_where1 IF
    @RecorderCount = 0 BEGIN EXEC SP_EXECUTESQL @SqlCount,N’@TotalCount INT
    OUTPUT,@TotalPageCount INT OUTPUT’, @TotalCount OUTPUT,@TotalPageCount
    OUTPUT END ELSE BEGIN SELECT @TotalCount = @RecorderCount END IF
    @PageIndex CEILING((@TotalCount+0.0)/@PageSize) BEGIN SET @PageIndex =
    CEILING((@TotalCount+0.0)/@PageSize) END IF @PageIndex = 1 OR @PageIndex
    = CEILING((@TotalCount+0.0)/@PageSize) BEGIN IF @PageIndex = 1
    –返回第一页数据 BEGIN SET @Sql = ‘SELECT TOP ‘ + STR(@PageSize) + ‘ ‘ +
    @FieldList + ‘ FROM ‘ + @TableName + @new_where1 + @new_order1 END IF
    @PageIndex = CEILING((@TotalCount+0.0)/@PageSize) –返回最后一页数据
    BEGIN SET @Sql = ‘SELECT TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘
    FROM (‘ + ‘SELECT TOP ‘ +
    STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize)) + ‘ ‘ + @FieldList
  • ‘ FROM ‘ + @TableName + @new_where1 + @new_order2 + ‘ ) AS TMP ‘ +
    @new_order1 END END ELSE BEGIN IF @SortType = 1 –仅主键正序排序 BEGIN
    IF @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)/2 –正向检索 BEGIN
    SET @Sql = ‘SELECT TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ‘
  • @TableName + @new_where2 + @PrimaryKey + ‘ ‘ + ‘(SELECT MAX(‘ +
    @PrimaryKey + ‘) FROM (SELECT TOP ‘ + STR(@PageSize*(@PageIndex-1)) + ‘
    ‘ + @PrimaryKey + ‘ FROM ‘ + @TableName + @new_where1 + @new_order1 +’
    ) AS TMP) ‘+ @new_order1 END ELSE –反向检索 BEGIN SET @Sql = ‘SELECT
    TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM (‘ + ‘SELECT TOP ‘ +
    STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ‘ + @TableName + @new_where2
  • @PrimaryKey + ‘ ‘ + ‘(SELECT MIN(‘ + @PrimaryKey + ‘) FROM (SELECT TOP
    ‘ + STR(@TotalCount-@PageSize*@PageIndex) + ‘ ‘ + @PrimaryKey + ‘ FROM
    ‘ + @TableName + @new_where1 + @new_order2 +’ ) AS TMP) ‘+
    @new_order2 + ‘ ) AS TMP ‘ + @new_order1 END END IF @SortType = 2
    –仅主键反序排序 BEGIN IF @PageIndex =
    CEILING((@TotalCount+0.0)/@PageSize)/2 –正向检索 BEGIN SET @Sql =
    ‘SELECT TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ‘ +
    @TableName + @new_where2 + @PrimaryKey + ‘ ‘ + ‘(SELECT MIN(‘ +
    @PrimaryKey + ‘) FROM (SELECT TOP ‘ + STR(@PageSize*(@PageIndex-1)) + ‘
    ‘ + @PrimaryKey +’ FROM ‘+ @TableName + @new_where1 + @new_order1 + ‘)
    AS TMP) ‘+ @new_order1 END ELSE –反向检索 BEGIN SET @Sql = ‘SELECT TOP
    ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM (‘ + ‘SELECT TOP ‘ +
    STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ‘ + @TableName + @new_where2
  • @PrimaryKey + ‘ ‘ + ‘(SELECT MAX(‘ + @PrimaryKey + ‘) FROM (SELECT TOP
    ‘ + STR(@TotalCount-@PageSize*@PageIndex) + ‘ ‘ + @PrimaryKey + ‘ FROM
    ‘ + @TableName + @new_where1 + @new_order2 +’ ) AS TMP) ‘+
    @new_order2 + ‘ ) AS TMP ‘ + @new_order1 END END IF @SortType = 3
    –多列排序,必须包含主键,且放置最后,否则不处理 BEGIN IF CHARINDEX(‘,’
  • @PrimaryKey + ‘ ‘,’,’ + @Order) = 0 BEGIN PRINT(‘ERR_02’) RETURN END
    IF @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)/2 –正向检索 BEGIN
    SET @Sql = ‘SELECT TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM (
    ‘ + ‘SELECT TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ( ‘ + ‘
    SELECT TOP ‘ + STR(@PageSize*@PageIndex) + ‘ ‘ + @FieldList + ‘ FROM ‘
  • @TableName + @new_where1 + @new_order1 + ‘ ) AS TMP ‘ + @new_order2
  • ‘ ) AS TMP ‘ + @new_order1 END ELSE –反向检索 BEGIN SET @Sql =
    ‘SELECT TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ( ‘ + ‘SELECT
    TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ( ‘ + ‘ SELECT TOP ‘
  • STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ‘ ‘ + @FieldList +
    ‘ FROM ‘ + @TableName + @new_where1 + @new_order2 + ‘ ) AS TMP ‘ +
    @new_order1 + ‘ ) AS TMP ‘ + @new_order1 END END END EXEC(@Sql) GO
    如何用vc#调用上面的存储过程 12阅读全文

Create PROC P_viewPage /**//* nzperfect [no_mIss]
高效通用分页存储过程(双向检索) 2007.5.7 QQ:34813284
敬告:适用于单一主键或存在唯一值列的表或视图
ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围 */
@TableName VARCHAR(200), –表名 @FieldList VARCHAR(2000),
–显示列名,如果是全部字段则为* @PrimaryKey VARCHAR(100),
–单一主键或唯一值键 @Where VARCHAR(2000), –查询条件
不含’where’字符,如id10 and len(userid)9 @Order VARCHAR(1000), –排序
不含’order by’字符,如id asc,userid desc,必须指定asc或desc
–注意当@SortType=3时生效,记住一定要在最后加上主键,否则会让你比较郁闷
@SortType INT, –排序规则 1:正序asc 2:倒序desc 3:多列排序方法
@RecorderCount INT, –记录总数 0:会返回总记录 @PageSize INT,
–每页输出的记录数 @PageIndex INT, –当前页数 @TotalCount INT OUTPUT ,
–记返回总记录 @TotalPageCount INT OUTPUT –返回总页数 AS SET NOCOUNT ON
IF ISNULL(@TotalCount,”) = ” SET @TotalCount = 0 SET @Order =
RTRIM(LTRIM(@Order)) SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey)) SET
@FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),’ ‘,”) WHILE CHARINDEX(‘,
‘,@Order) 0 or CHARINDEX(‘ ,’,@Order) 0 BEGIN SET @Order =
REPLACE(@Order,’, ‘,’,’) SET @Order = REPLACE(@Order,’ ,’,’,’) END IF
ISNULL(@TableName,”) = ” or ISNULL(@FieldList,”) = ” or
ISNULL(@PrimaryKey,”) = ” or @SortType 1 or @SortType 3 or
@RecorderCount 0 or @PageSize 0 or @PageIndex 0 BEGIN PRINT(‘ERR_00′)
RETURN END IF @SortType = 3 BEGIN IF (UPPER(RIGHT(@Order,4))!=’ ASC’ AND
UPPER(RIGHT(@Order,5))!=’ DESC’) BEGIN PRINT(‘ERR_02’) RETURN END END
DECLARE @new_where1 VARCHAR(1000) DECLARE @new_where2 VARCHAR(1000)
DECLARE @new_order1 VARCHAR(1000) DECLARE @new_order2 VARCHAR(1000)
DECLARE @new_order3 VARCHAR(1000) DECLARE @Sql VARCHAR(8000) DECLARE
@SqlCount NVARCHAR(4000) IF ISNULL(@where,”) = ” BEGIN SET
@new_where1 = ‘ ‘ SET @new_where2 = ‘ Where ‘ END ELSE BEGIN SET
@new_where1 = ‘ Where ‘ + @where SET @new_where2 = ‘ Where ‘ + @where

  • ‘ AND ‘ END IF ISNULL(@order,”) = ” or @SortType = 1 or @SortType =
    2 BEGIN IF @SortType = 1 BEGIN SET @new_order1 = ‘ orDER BY ‘ +
    @PrimaryKey + ‘ ASC’ SET @new_order2 = ‘ orDER BY ‘ + @PrimaryKey + ‘
    DESC’ END IF @SortType = 2 BEGIN SET @new_order1 = ‘ orDER BY ‘ +
    @PrimaryKey + ‘ DESC’ SET @new_order2 = ‘ orDER BY ‘ + @PrimaryKey + ‘
    ASC’ END END ELSE BEGIN SET @new_order1 = ‘ orDER BY ‘ + @Order END IF
    @SortType = 3 AND CHARINDEX(‘,’+@PrimaryKey+’ ‘,’,’+@Order)0 BEGIN SET
    @new_order1 = ‘ orDER BY ‘ + @Order SET @new_order2 = @Order + ‘,’ SET
    @new_order2 =
    REPLACE(REPLACE(@new_order2,’ASC,’,'{ASC},’),’DESC,’,'{DESC},’) SET
    @new_order2 =
    REPLACE(REPLACE(@new_order2,'{ASC},’,’DESC,’),'{DESC},’,’ASC,’) SET
    @new_order2 = ‘ orDER BY ‘ +
    SUBSTRING(@new_order2,1,LEN(@new_order2)-1) IF @FieldList ‘*’ BEGIN
    SET @new_order3 = REPLACE(REPLACE(@Order + ‘,’,’ASC,’,’,’),’DESC,’,’,’)
    SET @FieldList = ‘,’ + @FieldList WHILE CHARINDEX(‘,’,@new_order3)0
    BEGIN IF
    CHARINDEX(SUBSTRING(‘,’+@new_order3,1,CHARINDEX(‘,’,@new_order3)),’,’+@FieldList+’,’)0
    BEGIN SET @FieldList = @FieldList + ‘,’ +
    SUBSTRING(@new_order3,1,CHARINDEX(‘,’,@new_order3)) END SET
    @new_order3 =
    SUBSTRING(@new_order3,CHARINDEX(‘,’,@new_order3)+1,LEN(@new_order3))
    END SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList)) END END SET
    @SqlCount = ‘Select
    @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/’ +
    CAST(@PageSize AS VARCHAR)+’) FROM (Select * FROM ‘ + @TableName +
    @new_where1+’) AS T’ IF @RecorderCount = 0 BEGIN EXEC SP_EXECUTESQL
    @SqlCount,N’@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT’,
    @TotalCount OUTPUT,@TotalPageCount OUTPUT END ELSE BEGIN Select
    @TotalCount = @RecorderCount END IF @PageIndex
    CEILING((@TotalCount+0.0)/@PageSize) BEGIN SET @PageIndex =
    CEILING((@TotalCount+0.0)/@PageSize) END IF @PageIndex = 1 or @PageIndex
    = CEILING((@TotalCount+0.0)/@PageSize) BEGIN IF @PageIndex = 1
    –返回第一页数据 BEGIN SET @Sql = ‘Select * FROM (Select TOP ‘ +
    STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ‘ + @TableName + @new_where1
  • @new_order1 +’) AS TMP ‘ + @new_order1 END IF @PageIndex =
    CEILING((@TotalCount+0.0)/@PageSize) –返回最后一页数据 BEGIN SET @Sql =
    ‘Select TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM (‘ + ‘Select
    TOP ‘ + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize)) + ‘ ‘ +
    @FieldList + ‘ FROM ‘ + @TableName + @new_where1 + @new_order2 + ‘ )
    AS TMP ‘ + @new_order1 END END ELSE BEGIN IF @SortType = 1
    –仅主键正序排序 BEGIN IF @PageIndex =
    CEILING((@TotalCount+0.0)/@PageSize)/2 –正向检索 BEGIN SET @Sql =
    ‘Select TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ‘ +
    @TableName + @new_where2 + @PrimaryKey + ‘ ‘ + ‘(Select MAX(‘ +
    @PrimaryKey + ‘) FROM (Select TOP ‘ + STR(@PageSize*(@PageIndex-1)) + ‘
    ‘ + @PrimaryKey + ‘ FROM ‘ + @TableName + @new_where1 + @new_order1 +’
    ) AS TMP) ‘+ @new_order1 END ELSE –反向检索 BEGIN SET @Sql = ‘Select
    TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM (‘ + ‘Select TOP ‘ +
    STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ‘ + @TableName + @new_where2
  • @PrimaryKey + ‘ ‘ + ‘(Select MIN(‘ + @PrimaryKey + ‘) FROM (Select TOP
    ‘ + STR(@TotalCount-@PageSize*@PageIndex) + ‘ ‘ + @PrimaryKey + ‘ FROM
    ‘ + @TableName + @new_where1 + @new_order2 +’ ) AS TMP) ‘+
    @new_order2 + ‘ ) AS TMP ‘ + @new_order1 END END IF @SortType = 2
    –仅主键反序排序 BEGIN IF @PageIndex =
    CEILING((@TotalCount+0.0)/@PageSize)/2 –正向检索 BEGIN SET @Sql =
    ‘Select TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ‘ +
    @TableName + @new_where2 + @PrimaryKey + ‘ ‘ + ‘(Select MIN(‘ +
    @PrimaryKey + ‘) FROM (Select TOP ‘ + STR(@PageSize*(@PageIndex-1)) + ‘
    ‘ + @PrimaryKey +’ FROM ‘+ @TableName + @new_where1 + @new_order1 + ‘)
    AS TMP) ‘+ @new_order1 END ELSE –反向检索 BEGIN SET @Sql = ‘Select TOP
    ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM (‘ + ‘Select TOP ‘ +
    STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ‘ + @TableName + @new_where2
  • @PrimaryKey + ‘ ‘ + ‘(Select MAX(‘ + @PrimaryKey + ‘) FROM (Select TOP
    ‘ + STR(@TotalCount-@PageSize*@PageIndex) + ‘ ‘ + @PrimaryKey + ‘ FROM
    ‘ + @TableName + @new_where1 + @new_order2 +’ ) AS TMP) ‘+
    @new_order2 + ‘ ) AS TMP ‘ + @new_order1 END END IF @SortType = 3
    –多列排序,必须包含主键,且放置最后,否则不处理 BEGIN IF CHARINDEX(‘,’
  • @PrimaryKey + ‘ ‘,’,’ + @Order) = 0 BEGIN PRINT(‘ERR_02’) RETURN END
    IF @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)/2 –正向检索 BEGIN
    SET @Sql = ‘Select TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM (
    ‘ + ‘Select TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ( ‘ + ‘
    Select TOP ‘ + STR(@PageSize*@PageIndex) + ‘ ‘ + @FieldList + ‘ FROM ‘
  • @TableName + @new_where1 + @new_order1 + ‘ ) AS TMP ‘ + @new_order2
  • ‘ ) AS TMP ‘ + @new_order1 END ELSE –反向检索 BEGIN SET @Sql =
    ‘Select TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ( ‘ + ‘Select
    TOP ‘ + STR(@PageSize) + ‘ ‘ + @FieldList + ‘ FROM ( ‘ + ‘ Select TOP ‘
  • STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ‘ ‘ + @FieldList +
    ‘ FROM ‘ + @TableName + @new_where1 + @new_order2 + ‘ ) AS TMP ‘ +
    @new_order1 + ‘ ) AS TMP ‘ + @new_order1 END END END PRINT(@SQL)
    EXEC(@Sql)

自己写的一个

代码如下复制代码

USE [CaiLi]GO

/****** Object: StoredProcedure [dbo].[SqlPagination] Script
Date: 10/26/2011 11:40:46 ******/SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ONGO

CREATE PROCEDURE [dbo].[SqlPagination]

/*

***************************************************************

** 千万数量级分页存储过程 **

***************************************************************

参数说明:

1.Tables :表名称,视图

2.PrimaryKey :主关键字

3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc

4.CurrentPage :当前页码

5.PageSize :分页尺寸

6.Filter :过滤语句,不带Where

7.Group :Group语句,不带Group By

***************************************************************/

(

@Tables varchar(1000),

@PrimaryKey varchar(100),

@Sort varchar(200) = NULL,

@CurrentPage int = 1,

@PageSize int = 10,

@Fields varchar(1000) = ‘*’,

@Filter varchar(1000) = NULL,

@Group varchar(1000) = NULL)

AS

/*默认排序*/if @PrimaryKey IS NULL or @PrimaryKey = ”set
@PrimaryKey=’ID’

IF @Sort IS NULL or @Sort = ”

SET @Sort = @PrimaryKey

IF @Fields IS NULL or @Fields = ”

SET @Fields = ‘*’

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(‘,’,@Sort) 0 set @strSortColumn =
substring(@Sort,0,charindex(‘,’,@Sort)) elseset @strSortColumn = @SortIF
CHARINDEX(‘DESC’,@Sort)0

BEGIN

SET @strSortColumn = REPLACE(@strSortColumn, ‘DESC’, ”)

SET @operator = ‘=’

END

ELSE

BEGIN

IF CHARINDEX(‘ASC’,@Sort) 0BEGINSET @strSortColumn =
REPLACE(@strSortColumn, ‘ASC’, ”)

SET @operator = ‘=’ENDEND

IF CHARINDEX(‘.’, @strSortColumn) 0

BEGIN

SET @SortTable = SUBSTRING(@strSortColumn, 0,
CHARINDEX(‘.’,@strSortColumn))