SQL分页存储过程及调用方法

存储过程：

USE [HHWeb]
GO
/****** Object:  StoredProcedure [dbo].[P_Pageing]    Script Date: 05/08/2013 14:09:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[P_Pageing]

@tbname     nvarchar(50),           --要分页显示的表的名称

@FieldKey   nvarchar(100),            --用于定位记录的主键(唯一键)字段,可以是逗号分隔的多个字段

@NowPage int=1,                        --要显示的页码

@PageSize   int=20,                    --每页显示的记录条数

@FieldShow nvarchar(500)='',        --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段

@FieldOrder nvarchar(500)='',        --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序

@Where    nvarchar(500)='',            --查询条件

@Totalcount int OUTPUT                --总条数

AS

SET NOCount ON

--检查对象是否有效

IF OBJECT_ID(@tbname) IS NULL

BEGIN

    RAISERROR(N'对象"%s"不存在',1,16,@tbname)

    RETURN

END

IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0

    AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0

    AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0

BEGIN

    RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)

    RETURN

END

 

--分页字段检查

IF ISNULL(@FieldKey,N'')=''

BEGIN

    RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)

    RETURN

END

 

--其他参数检查及规范

IF ISNULL(@NowPage,0)<1 SET @NowPage=1

IF ISNULL(@PageSize,0)<1 SET @PageSize=20

IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'

IF ISNULL(@FieldOrder,N'')=N''

    SET @FieldOrder=N''

ELSE

    SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)


--计算分页显示的TOPN值

DECLARE @TopN varchar(20)
SELECT @TopN=(@NowPage-1)*@PageSize

--判断查询条件是否存在
DECLARE @WhereOne nvarchar(1000)
IF ISNULL(@Where,N'')=N''
BEGIN
    SET @WhereOne=N' '
    SET @Where=N'WHERE '+@FieldKey+N' not in(select top '+@TopN+N' '+@FieldKey+N' from '+@tbname+N' '+@FieldOrder+N')'
END
ELSE
BEGIN
    SET @WhereOne=N'WHERE '+@Where+N' '
    SET @Where=N'WHERE '+@Where+N' and '+@FieldKey+N' not in(select top '+@TopN+N' '+@FieldKey+N' from '+@tbname+N' WHERE '+@Where+N' '+@FieldOrder+N')'
END

IF @Totalcount IS NULL

BEGIN

    DECLARE @sql nvarchar(4000)

    SET @sql=N'SELECT @Totalcount=Count(*)'

        +N' FROM '+@tbname

        +N' '+@WhereOne

    EXEC sp_executesql @sql,N'@Totalcount int OUTPUT',@Totalcount OUTPUT
    
END


--执行查询
EXEC(N'SELECT TOP '+@PageSize+' '+@FieldShow+' FROM '+@tbname+' with(nolock) '+@Where+' '+@FieldOrder+'')

--DECLARE @s int
--exec P_Pageing 'DownloadCodes','ID',4,2,'','AddTime desc','DowncodeNumber=1',@s OUTPUT
--select @s 

在cs页面调用的方式,查询时会有条件进行查询，先把查询条件拼接

 #region //搜索查询
        public void initSearch()
        {
            StringBuilder Wherestr = new StringBuilder();
            SearchTitle = Request.Form["SearchTitle"].ToString();
            SearchPublishTimeS = Request.Form["SearchPublishTimeS"].ToString();
            SearchPublishTimeE = Request.Form["SearchPublishTimeE"].ToString();
            SearchNewClassID= Request.Form["SearchNewClassInfo"].ToString();
            SearchNewSource = Request.Form["SearchNewSource"].ToString();
            Wherestr.Append("1=1");
            if (!string.IsNullOrEmpty(SearchTitle))
                Wherestr.Append(" and Title like '%" + SearchTitle + "%'");
            if (!string.IsNullOrEmpty(SearchPublishTimeS))
                Wherestr.Append(" and PublishTime >= '" + SearchPublishTimeS + "'");
            if (!string.IsNullOrEmpty(SearchPublishTimeE))
                Wherestr.Append(" and PublishTime <='" + SearchPublishTimeE + "'");
            if (SearchNewClassID != "0")
                Wherestr.Append(" and NewClassID =" + SearchNewClassID + "");
            if (!string.IsNullOrEmpty(SearchNewSource))
                Wherestr.Append(" and NewSource like '%" + SearchNewSource + "%'");

            ViewState["NowPage"] = 1;
            ViewState["Where"] = Wherestr.ToString();
        }
        #endregion

调用分页存储过程

        public void BindPage(int NowPage, int PageSize)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tbname", SqlDbType.NVarChar,50),
                    new SqlParameter("@FieldKey", SqlDbType.NVarChar,1000),
                    new SqlParameter("@NowPage", SqlDbType.Int,4),
                    new SqlParameter("@PageSize", SqlDbType.Int,4),
                    new SqlParameter("@FieldShow", SqlDbType.NVarChar,1000),
                    new SqlParameter("@FieldOrder", SqlDbType.NVarChar,1000),
                    new SqlParameter("@Where", SqlDbType.NVarChar,1000),
                    new SqlParameter("@Totalcount", SqlDbType.Int,4)};
            parameters[0].Value = "News";
            parameters[1].Value = "ID";
            parameters[2].Value = NowPage;
            parameters[3].Value = PageSize;
            parameters[4].Value = "";
            parameters[5].Value = "Sort desc,PublishTime desc";
            parameters[6].Value =ViewState["Where"].ToString();
            parameters[7].Direction = ParameterDirection.Output;
            Repeater1.DataSource = HHDBUtility.DbHelperSQL.RunProcedure("P_Pageing", parameters);
            Repeater1.DataBind();
            AspNetPager1.AlwaysShow = true;
            AspNetPager1.PageSize = PageSize;
            AspNetPager1.RecordCount =Convert.ToInt32(parameters[7].Value);
        }