Heim >Datenbank >MySQL-Tutorial >sql2000/2005中高效分页存储过程实例

sql2000/2005中高效分页存储过程实例

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOriginal: 2016-06-07 17:49:01952Durchsuche

文章有二个实例一个是sql2000的分页存储过程一个是sql2005的分页存储过程，有需要的同学可以拿去看看。

代码如下

复制代码

CREATE PROCEDURE [dbo].[ProcCustomPage]
    (
     @Table_Name varchar(5000),    --表名
     @Sign_Record varchar(50),     --主键
     @Filter_Condition varchar(1000),    --筛选条件,不带where
     @Page_Size int,     --页大小
     @Page_Index int,       --页索引
   @TaxisField varchar(1000), --排序字段
     @Taxis_Sign int,     --排序方式 1为 DESC, 0为 ASC
@Find_RecordList varchar(1000),   --查找的字段
     @Record_Count int     --总记录数
     )
     AS
      BEGIN
      DECLARE @Start_Number int
      DECLARE @End_Number int
      DECLARE @TopN_Number int
     DECLARE @sSQL varchar(8000)
if(@Find_RecordList='')
BEGIN
SELECT @Find_RecordList='*'
END
     SELECT @Start_Number =(@Page_Index-1) * @Page_Size
      IF @Start_Number      SElECT @Start_Number=0
      SELECT @End_Number=@Start_Number+@Page_Size
      IF @End_Number>@Record_Count
     SELECT @End_Number=@Record_Count
     SELECT @TopN_Number=@End_Number-@Start_Number
     IF @TopN_Number      SELECT @TopN_Number=0
      print @TopN_Number
     print @Start_Number
     print @End_Number
     print @Record_Count
IF @TaxisField=''
begin
@TaxisField=@Sign_Record
end
     IF @Taxis_Sign=0
       BEGIN
         IF @Filter_Condition=''
         BEGIN
           SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
           WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
            WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
          ORDER BY '+@TaxisField+') order by '+@TaxisField+' DESC)order by '+@TaxisField
         END
        ELSE
        BEGIN
        SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
     WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField
         END
      END
    ELSE
      BEGIN
      IF @Filter_Condition=''
        BEGIN
          SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
     ORDER BY '+@TaxisField+' DESC) order by '+@TaxisField+')order by '+@TaxisField+' DESC'
     END
      ELSE
      BEGIN
        SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+'
     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+'
     WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC'
     END
      END
      EXEC (@sSQL)
      IF @@ERROR0
      RETURN -3
     RETURN 0
     END

     PRINT @sSQL
GO

sql2005

代码如下

复制代码

CREATE PROCEDURE [dbo].[GetRecordFromPage]
@SelectList VARCHAR(2000), --欲选择字段列表
@TableSource VARCHAR(100), --表名或视图表
@SearchCondition VARCHAR(2000), --查询条件
@OrderExpression VARCHAR(1000), --排序表达式
@PageIndex INT = 1, --页号,从0开始
@PageSize INT = 10 --页尺寸
AS
BEGIN
IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ''
BEGIN
SET @SelectList = '*'
END
PRINT @SelectList

SET @SearchCondition = ISNULL(@SearchCondition,'')
SET @SearchCondition = LTRIM(RTRIM(@SearchCondition))
IF @SearchCondition ''
BEGIN
IF UPPER(SUBSTRING(@SearchCondition,1,5)) 'WHERE'
BEGIN
SET @SearchCondition = 'WHERE ' + @SearchCondition
END
END
PRINT @SearchCondition

SET @OrderExpression = ISNULL(@OrderExpression,'')
SET @OrderExpression = LTRIM(RTRIM(@OrderExpression))
IF @OrderExpression ''
BEGIN
IF UPPER(SUBSTRING(@OrderExpression,1,5)) 'WHERE'
BEGIN
SET @OrderExpression = 'ORDER BY ' + @OrderExpression
END
END
PRINT @OrderExpression

IF @PageIndex IS NULL OR @PageIndex BEGIN
SET @PageIndex = 1
END
PRINT @PageIndex
IF @PageSize IS NULL OR @PageSize BEGIN
SET @PageSize = 10
END
PRINT @PageSize

DECLARE @SqlQuery VARCHAR(4000)

SET @SqlQuery='SELECT '+@SelectList+',RowNumber
FROM
(SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber
FROM '+@TableSource+' '+ @SearchCondition +') AS RowNumberTableSource
WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR)
+ ' AND ' +
CAST((@PageIndex * @PageSize) AS VARCHAR)
-- ORDER BY ' + @OrderExpression
PRINT @SqlQuery
SET NOCOUNT ON
EXECUTE(@SqlQuery)
SET NOCOUNT OFF

RETURN @@RowCount
END

Stellungnahme：

Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn

Vorheriger Artikel：创建数据库关系图失败解决方法Nächster Artikel：sqlsever t-sql判断输入的数字重复出现的次数

In Verbindung stehende Artikel

Mehr sehen