SQL2005存储过程分页
存储过程代码如下：
@Tables varchar(1000), --表名,多红表是请使用 tA a inner join tB b On a.AID = b.AID
@PK varchar(100), --主键，可以带表头 a.AID
@Sort varchar(200) = '', --排序字段
@PageNumber int = 1, --开始页码
@PageSize int = 10, --页大小
@Fields varchar(1000) = '*',--读取字段
@Filter varchar(1000) = NULL,--Where条件
@Group varchar(1000) = NULL, --分组
@isCount bit = 0 --1 --是否获得总记录数
AS
--
--select * from GL_NEWS order by GN_UPDATE_DATE DESC
--exec Pg_Paging @Tables = 'tb_NewsInfo', @PK = 'News_ID', @Sort = 'News_ID DESC', @PageNumber = 2, @PageSize = 15,@Fields = '*', @Group = '', @isCount = 0
DECLARE @strFilter varchar(2000)
declare @sql varchar(8000)
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
END
ELSE
BEGIN
SET @strFilter = ''
END
if @isCount = 1 --只获得记录条数
begin
set @sql = 'SELECT Count(*) FROM ' + @Tables + @strFilter
end
else
begin
if @Sort = ''
set @Sort = @PK + ' DESC '
IF @PageNumber < 1
SET @PageNumber = 1
if @PageNumber = 1 --第一页提高性能
begin
set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ ' from ' + @Tables + ' ' + @strFilter + ' ORDER BY '+ @Sort
end
else
begin
/**//*Execute dynamic query*/
DECLARE @START_ID varchar(50)
DECLARE @END_ID varchar(50)
SET @START_ID = convert(varchar(50),(@PageNumber - 1) * @PageSize + 1)
SET @END_ID = convert(varchar(50),@PageNumber * @PageSize)
set @sql = ' SELECT '+@Fields+ '
FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum,
'+@Fields+ '
FROM '+@Tables+') AS D
WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort
END
END
--print @sql
EXEC(@sql)
可以通过封装一个静态函数来执行：（EnterpriseLibrary3。1）
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.Xml;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
namespace Glenet.EjiaShop.SqlData
{
/// <summary>
/// Pageing
/// </summary>
public class Pageing
{
public Pageing()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
#region Pg_Paging
/// <summary>
/// Pg_Paging
/// </summary>
/// <param name="Tables"></param>
/// <param name="PK"></param>
/// <param name="Filter"></param>
/// <returns></returns>
public static int Pg_PageCount(string Tables,string PK,string Filter)
{
//创建数据库实例
Database db = DatabaseFactory.CreateDatabase();
//获得命令
string sqlCommand = "Pg_Paging";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
//设置参数
db.AddInParameter(dbCommand, "Tables", DbType.String, Tables);
db.AddInParameter(dbCommand, "PK", DbType.String, PK);
db.AddInParameter(dbCommand, "Sort", DbType.String, "");
db.AddInParameter(dbCommand, "PageNumber", DbType.Double, 0);
db.AddInParameter(dbCommand, "PageSize", DbType.Double, 0);
db.AddInParameter(dbCommand, "Fields", DbType.String, "*");
db.AddInParameter(dbCommand, "Filter", DbType.String, Filter);
db.AddInParameter(dbCommand, "Group", DbType.String, "");
db.AddInParameter(dbCommand, "isCount", DbType.Boolean, 1);
//执行
return int.Parse(db.ExecuteScalar(dbCommand).ToString());
//取得输出参数
}
#endregion
#region Pg_Paging
/// <summary>
/// Pg_Paging
/// </summary>
/// <param name="Tables"></param>
/// <param name="PK"></param>
/// <param name="Sort"></param>
/// <param name="PageNumber"></param>
/// <param name="PageSize"></param>
/// <param name="Fields"></param>
/// <param name="Filter"></param>
/// <param name="Group"></param>
/// <returns></returns>
public static DataSet Pg_Paging(string Tables,string PK,string Sort,int PageNumber,int PageSize,string Fields,string Filter,string Group)
{
//创建数据库实例
Database db = DatabaseFactory.CreateDatabase();
//获得命令
string sqlCommand = "Pg_Paging";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
//设置参数
db.AddInParameter(dbCommand, "Tables", DbType.String, Tables);
db.AddInParameter(dbCommand, "PK", DbType.String, PK);
db.AddInParameter(dbCommand, "Sort", DbType.String, Sort);
db.AddInParameter(dbCommand, "PageNumber", DbType.Double, PageNumber);
db.AddInParameter(dbCommand, "PageSize", DbType.Double, PageSize);
db.AddInParameter(dbCommand, "Fields", DbType.String, Fields);
db.AddInParameter(dbCommand, "Filter", DbType.String, Filter);
db.AddInParameter(dbCommand, "Group", DbType.String, Group);
db.AddInParameter(dbCommand, "isCount", DbType.Boolean, 0);
//执行
return db.ExecuteDataSet(dbCommand);
}
#endregion
}
}
AspNetPager1.RecordCount = Glenet.EjiaShop.SqlData.Pageing.Pg_PageCount("tb_NewsInfo", "News_ID", filter);
using (DataSet ds = Glenet.EjiaShop.SqlData.Pageing.Pg_Paging("tb_NewsInfo", "News_ID", "News_AddTime DESC", AspNetPager1.CurrentPageIndex, AspNetPager1.PageSize, "*", filter, ""))
{
this.Re_ContentList.DataSource = ds.Tables[0].DefaultView;
this.Re_ContentList.DataBind();
AspNetPager1.CustomInfoText = "记录总数：<font color=\"#00007f\"><b>" + AspNetPager1.RecordCount.ToString() + "</b></font>";
AspNetPager1.CustomInfoText += " 总页数：<font color=\"#00007f\"><b>" + AspNetPager1.PageCount.ToString() + "</b></font>";
AspNetPager1.CustomInfoText += " 当前页：<font color=\"red\"><b>" + AspNetPager1.CurrentPageIndex.ToString() + "</b></font>";
}
相当方便。
第二种
--sql 2000 分页存储过程
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<=0
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<=0
SELECT @TopN_Number=0
print @TopN_Number
print @Start_Number
print @End_Number
print @Record_Count
IF @TaxisField=''
begin
select @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 @@ERROR<>0
RETURN -3
RETURN 0
END
PRINT @sSQL
GO
CREATE PROCEDURE [dbo].[GetRecordFromPage2005]
@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 < 1
BEGIN
SET @PageIndex = 1
END
PRINT @PageIndex
IF @PageSize IS NULL OR @PageSize < 1
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
浙公网安备 33010602011771号