SQL SERVER 2005分頁儲存過程

       看了很多的分頁儲存過程，感覺用起來還是沒有自己親自寫的用起來感覺親切，雖然寫的比較爛，但是對這個方面的提高還是隻有靠自己親自動手才知道其中的微妙之處，哈哈！
CREATE PROCEDURE     [dbo].[TopPageList]
    @strTable       varchar(200),   --表名 ("@strTable", "myUser");
    @strColumn      varchar(50),    --按該列來進行分頁 ("@strColumn", "UserId");
    @strOrderColumn varchar(50),    --排序欄位order by XXX desc
    @intOrder     int,--排序的順序 0 升序 1降序
    @strColumnlist varchar(150) , --要查詢出的欄位列表,*表示全部欄位 cmd.Parameters.Add("@strColumnlist", "*");
    @strWhere       varchar(800)='', --查詢條件 cmd.Parameters.Add("@strWhere", "");
    @intPageSize    int,           --每頁記錄數 cmd.Parameters.Add("@intPageSize", 15);
    @intPageNum     int,           --指定頁 cmd.Parameters.Add("@intPageNum", 5);
-- @intPageCount   int   OUTPUT , --總頁數 SqlParameter paramPageCount = cmd.Parameters.Add("@intPageCount", SqlDbType.Int);
   -- paramPageCount.Direction = ParameterDirection.Output;
    @itemCount      int   OUTPUT
--   @doCount bit = 0, -- 返回, 非值則返回記錄總數
AS
--設定相應的空格

--設定DESC ASC
if @intOrder=0 --0升序
     set @strOrderColumn=' order by '+@strOrderColumn
else            --降序
     set @strOrderColumn=' order by '+@strOrderColumn +' desc '

DECLARE   @sql    nvarchar(2000) --用於構造SQL語句
DECLARE   @where1 varchar(800)   --構造條件語句
DECLARE   @where2 varchar(800)   --構造條件語句
IF   @strWhere   is   null   or   rtrim(@strWhere)=''
-- 為了避免SQL關鍵字與欄位、表名等連在一起，首先為傳入的變數新增空格
BEGIN --沒有查詢條件
      SET   @where1=' WHERE '
      SET   @where2=' '
END
ELSE
BEGIN --有查詢條件
      SET   @where1=' WHERE ('+@strWhere+') AND '
      SET   @where2=' WHERE ('+@strWhere+') '
END
------構造SQL語句，計算總頁數。計算公式為總頁數= Ceiling ( 記錄個數/ 頁大小)
   --計算總項數
SET   @sql='SELECT   @itemCount=COUNT('+@strColumn+') from   '+@strTable +@where2
print(@sql)
EXEC sp_executesql @sql,N'@itemCount int   OUTPUT',@itemCount   OUTPUT
   --   1：直接計算    2：自己寫個分頁控制元件裡面設定一下也可以~！
-- set @intPageCount =floor(cast(@itemCount as float)/@intPageSize)
--   if @intPageCount --   set @intPageCount =@intPageCount +1
--


--執行SQL語句，計算總頁數，並將其放入@intPageCount變數中

--將總頁數放到查詢返回記錄集的第一個欄位前，此語句可省略
SET @strColumnlist=' '+ Cast(@itemCount as varchar(30)) + ' as itemCount,' +' '+ @strColumnlist
--+ Cast(@intPageCount as varchar(30)) + ' as PageCount,'
SET @sql='SELECT TOP '+ CAST(@intPageSize   AS   varchar) + @strColumnlist +
                ' FROM ' + @strTable + @where1 + ' '+
                @strColumn + ' not in '+
                ' (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1) AS varchar) + ' ' +
                @strColumn + ' FROM '+ @strTable+@where2+@strOrderColumn+') ' + @strOrderColumn
print(@sql)

--ELSE
--     begin   --構造降序的SQL ---針對2個表的時候會出現聚合函式的異常--適合單個表格的資料庫分頁操作
--     SET @sql='SELECT TOP '+ CAST(@intPageSize   AS   varchar) + @strColumnlist +
--               ' FROM ' + @strTable + @where1 + ' '+
--              @strColumn + ''+@strColumn+') '+
--              ' FROM (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1) AS varchar) + ' ' +
--              @strColumn + ' FROM '+ @strTable+@where2+@strOrderColumn+') as tblTmp)' + @strOrderColumn
--    print(@sql)
--     end
IF   @intPageNum=1--第一頁
      SET   @sql='SELECT   TOP   '+CAST(@intPageSize   AS   varchar) + @strColumnlist + ' FROM '+@strTable+
                 @where2+@strOrderColumn
--END
--PRINT   @sql
print(@sql)
exec(@sql)

public static void BindingContent(string strTable, string strColumn, string strOrderColumn, int intOrder, string strColumnlist, string strWhere, IChangePageStored changePage)
        {

            SqlParameter[] paras=new SqlParameter[9];
            paras[0] = new SqlParameter("@strTable" ,SqlDbType.VarChar);
            paras[0].Value = strTable;
            paras[1] = new SqlParameter("@strColumn", SqlDbType.VarChar);
            paras[1].Value = strColumn;
            paras[2] = new SqlParameter("@strOrderColumn", SqlDbType.VarChar);
            paras[2].Value = strOrderColumn;
            paras[3] = new SqlParameter("@strColumnlist", SqlDbType.VarChar);
            paras[3].Value = strColumnlist;
            paras[4] = new SqlParameter("@intOrder", SqlDbType.Int);
            paras[4].Value = intOrder;
            paras[5] = new SqlParameter("@strWhere", SqlDbType.VarChar);
            paras[5].Value = strWhere;
            paras[6] = new SqlParameter("@intPageSize", SqlDbType.Int);
            paras[6].Value = changePage.PageSize;
            paras[7] = new SqlParameter("@intPageNum", SqlDbType.Int);
            paras[7].Value = changePage.CurrentPage ;
         //   paras[8] = new SqlParameter("@intPageCount", SqlDbType.Int);
         //   paras[8].Direction = ParameterDirection.Output;
            paras[8] = new SqlParameter("@itemCount", SqlDbType.Int);
            paras[8].Direction = ParameterDirection.Output;
            DataSet ds = DBTool.ExecuteDataset(CommandType.StoredProcedure, "TopPageList", paras);

          /* @intPageCount   int   OUTPUT , --總頁數 SqlParameter paramPageCount = cmd.Parameters.Add("@intPageCount", SqlDbType.Int);
   -- paramPageCount.Direction = ParameterDirection.Output;

                @strTable = N'zhq_in_content c INNER JOIN zhp_in_columns m ON c.columns_id=m.columns_id',
  @strColumn = N'c.content_id',
  @strOrderColumn = N'c.createdate',
  @intOrder = 1,
  @strColumnlist = N'*',
  @strWhere = N'c.status=0 AND c.del=0',
  @intPageSize = 20,
  @intPageNum =100,*/
            changePage.DataSource = ds.Tables[0].DefaultView;// 設定分頁控制元件的資料
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                changePage.RecordCount = int.Parse(ds.Tables[0].Rows[0]["itemCount"].ToString());
               // changePage.PageCount = int.Parse(ds.Tables[0].Rows[0]["PageCount"].ToString());
            }
            if (changePage.DataUI.GetType().BaseType.Name == "BaseDataList")
            {
                changePage.DataUI.DataSource = changePage.DataSource;//設定資料來源控制元件的資料
                changePage.DataUI.DataBind();

            }

}

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-622798/，如需轉載，請註明出處，否則將追究法律責任。

SQL SERVER 2005分頁儲存過程

相關文章