本条还以为不错的八个积存进度

/****** 对象: StoredProcedure [dbo].[QueryPagination]
脚本日期: 05/14/2012 08:57:04 ******/SET ANSI_NULLS ONGOSET
QUOTED_IDENTIFIER ONGO

  • ‘ FROM ‘ + @TableName + @new_where1 + @new_order1 END IF @PageIndex
    >= CEILING/@PageSize) –返回最后一页数据 BEGIN SET @Sql = ‘SELECT TOP
    ‘ + STR + ‘ ‘ + @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/@PageSize)/2 –正向检索 BEGIN SET @Sql =
    ‘SELECT TOP ‘ + STR + ‘ ‘ + @FieldList + ‘ FROM ‘ + @TableName +
    @new_where2 + @PrimaryKey + ‘ > ‘ + ‘(SELECT MAX FROM (SELECT TOP ‘
  • STR(@PageSize* + ‘ ‘ + @PrimaryKey + ‘ FROM ‘ + @TableName +
    @new_where1 + @new_order1 +’ ) AS TMP) ‘+ @new_order1 END ELSE
    –反向检索 BEGIN SET @Sql = ‘SELECT TOP ‘ + STR + ‘ ‘ + @FieldList + ‘
    FROM (‘ + ‘SELECT TOP ‘ + STR + ‘ ‘ + @FieldList + ‘ FROM ‘ + @TableName
  • @new_where2 + @PrimaryKey + ‘ < ‘ + ‘(SELECT MIN 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/@PageSize)/2
    –正向检索 BEGIN SET @Sql = ‘SELECT TOP ‘ + STR + ‘ ‘ + @FieldList + ‘
    FROM ‘ + @TableName + @new_where2 + @PrimaryKey + ‘ < ‘ + ‘(SELECT
    MIN FROM (SELECT TOP ‘ + STR(@PageSize* + ‘ ‘ + @PrimaryKey +’ FROM ‘+
    @TableName + @new_澳门新葡8455最新网站,where1 + @new_order1 + ‘) AS TMP) ‘+ @new_order1
    END ELSE –反向检索 BEGIN SET @Sql = ‘SELECT TOP ‘ + STR + ‘ ‘ +
    @FieldList + ‘ FROM (‘ + ‘SELECT TOP ‘ + STR + ‘ ‘ + @FieldList + ‘ FROM
    ‘ + @TableName + @new_where2 + @PrimaryKey + ‘ > ‘ + ‘(SELECT MAX
    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 RETURN
    END IF @PageIndex <= CEILING/@PageSize)/2 –正向检索 BEGIN SET @Sql =
    ‘SELECT TOP ‘ + STR + ‘ ‘ + @FieldList + ‘ FROM ( ‘ + ‘SELECT TOP ‘ +
    STR + ‘ ‘ + @FieldList + ‘ FROM ( ‘ + ‘ SELECT TOP ‘ + STR + ‘ ‘ +
    @FieldList + ‘ FROM ‘ + @TableName + @new_where1 + @new_order1 + ‘ )
    AS TMP ‘ + @new_order2 + ‘ ) AS TMP ‘ + @new_order1 END ELSE
    –反向检索 BEGIN SET @Sql = ‘SELECT TOP ‘ + STR + ‘ ‘ + @FieldList + ‘
    FROM ( ‘ + ‘SELECT TOP ‘ + STR + ‘ ‘ + @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
    GO 如何用vc#调用上面的存储过程 12下一页阅读全文
  • ‘ FROM ‘ + @TableName + @new_where1 + @new_order2 + ‘ ) AS TMP ‘ +
    @new_order1 ENDENDELSEBEGINIF @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 ENDIF @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 ENDIF @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 ENDENDEXEC(@Sql)

复制代码 代码如下: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, –表名 @FieldList VARCHAR,
–显示列名,如果是全部字段则为* @PrimaryKey VARCHAR,
–单一主键或唯一值键 @Where VARCHAR, –查询条件
不含’where’字符,如id>10 and len>9 @Order VARCHAR, –排序
不含’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 = ” SET @TotalCount = 0 SET @Order = RTRIM SET @PrimaryKey =
RTRIM SET @FieldList = REPLACE(RTRIM,’ ‘,”) WHILE CHARINDEX > 0 OR
CHARINDEX > 0 BEGIN SET @Order = REPLACE SET @Order = REPLACE END IF
ISNULL = ” OR ISNULL = ” OR ISNULL = ” OR @SortType < 1 OR
@SortType >3 OR @RecorderCount < 0 OR @PageSize < 0 OR
@PageIndex < 0 BEGIN PRINT RETURN END IF @SortType = 3 BEGIN IF )!=’
ASC’ AND UPPER!=’ DESC’) BEGIN PRINT RETURN END END DECLARE @new_where1
VARCHAR DECLARE @new_where2 VARCHAR DECLARE @new_order1 VARCHAR
DECLARE @new_order2 VARCHAR DECLARE @new_order3 VARCHAR DECLARE @Sql
VARCHAR DECLARE @SqlCount NVARCHAR IF ISNULL = ” 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 = ” 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 IF @FieldList
<> ‘*’ BEGIN SET @new_order3 = REPLACE(REPLACE(@Order +
‘,’,’ASC,’,’,’),’DESC,’,’,’) SET @FieldList = ‘,’ + @FieldList WHILE
CHARINDEX>0 BEGIN IF
CHARINDEX(SUBSTRING(‘,’+@new_order3,1,CHARINDEX,’,’+@FieldList+’,’)>0
BEGIN SET @FieldList = @FieldList + ‘,’ +
SUBSTRING(@new_order3,1,CHARINDEX END SET @new_order3 =
SUBSTRING(@new_order3,CHARINDEX+1,LEN END SET @FieldList =
SUBSTRING(@FieldList,2,LEN END END SET @SqlCount = ‘SELECT
@TotalCount=COUNT,@TotalPageCount=CEILING/’ + CAST+’) 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/@PageSize)
BEGIN SET @PageIndex = CEILING/@PageSize) END IF @PageIndex = 1 OR
@PageIndex >= CEILING/@PageSize) BEGIN IF @PageIndex = 1
–返回第一页数据 BEGIN SET @Sql = ‘SELECT TOP ‘ + STR + ‘ ‘ + @FieldList

Set xact_abort onBegin Tran

If @strWhereClause ” set @strWhere = @strWhereClause;

set @strWhere = ”; — Where 语句

/****** 对象: StoredProcedure [dbo].[P_viewPage] 脚本日期:
05/14/2012 08:49:34 ******/SET ANSI_NULLS ONGOSET
QUOTED_IDENTIFIER ONGO

文章也是从朋友那里抄来的两个不错的sql分页存储过程实现,听说效率比较高的一个存储过程,有需要的同学可以了解一下或提供更好的解决方案。

SET @strSQL = ‘SELECT COUNT(1) FROM ‘ + @strTableList + ‘ ‘ + @strWhere;

Set @setvalue_error = @@errorIf @setvalue_error0 Begin Set
@intTotoRecords = -1; GOTO DoNext; Rollback Tran End Else Begin Commit
Tran GOTO DoNext; End

代码如下复制代码

–输出内容SELECT @TotalCount as N’@TotalCount’, @TotalPageCount as
N’@TotalPageCount’

代码如下复制代码

exec(‘DECLARE cur_t CURSOR FOR ‘+ @strSQL)

errPageSize: return -1;

If @@error0 return @@error; Else return 0;

相关文章

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图