学步园

最近在asp.net 用到的sqlserver存储过程分页中显示数据时遇到关于排序问题。

网上的一些分页存储过程只支持排序列为int 类型，对于实际开发中很多时候都需要对DateTime类型，varchar类型进行排序。

下面分享一下可以支持任意数据类型的列进行排序的存储过程。

Create PROCEDURE  sp_viewByPage
@TblName  varchar(50),              — 表名
@FldName  varchar(50),       — 排序列
@PageSize int,      – 每页记录数
@PageIndex int,      – 指定页
@strWhere  varchar(1000),   – 查询条件
@OrderType bit = 0        — 设置排序类型, 非 0 值则降序
AS
Begin
Declare @Sql nvarchar(1000)
if @strWhere !=”        –是否where
if @OrderType =’0′
begin
Set @Sql=’SELECT SerialNumber,* FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY ‘+@FldName +’ ASC) AS SerialNumber FROM ‘+@TblName +’ where ‘+@strWhere+’) AS T WHERE T.SerialNumber > ‘+str((@PageIndex-1) * @PageSize)+’ and T.SerialNumber <= ‘ + str(@PageIndex * @PageSize)
Exec sp_executesql @Sql
end
else
begin
Set @Sql=’SELECT SerialNumber,* FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY ‘+@FldName +’ DESC) AS SerialNumber FROM ‘+@TblName +’ where ‘+@strWhere+’) AS T WHERE T.SerialNumber > ‘+str((@PageIndex-1) * @PageSize)+’ and T.SerialNumber <= ‘ + str(@PageIndex * @PageSize)
Exec sp_executesql @Sql
end
else
if @OrderType =’0′
begin
Set @Sql=’SELECT SerialNumber,* FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY ‘+@FldName +’ ASC) AS SerialNumber FROM ‘+@TblName +’) AS T WHERE T.SerialNumber > ‘+str((@PageIndex-1) * @PageSize)+’ and T.SerialNumber <= ‘ + str(@PageIndex * @PageSize)
Exec sp_executesql @Sql
end
else
begin
Set @Sql=’SELECT SerialNumber,* FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY ‘+@FldName +’ DESC) AS SerialNumber FROM ‘+@TblName +’) AS T WHERE T.SerialNumber > ‘+str((@PageIndex-1) * @PageSize)+’ and T.SerialNumber <= ‘ + str(@PageIndex * @PageSize)
Exec sp_executesql @Sql
end
End

exec sp_viewByPage1 ‘hope_T_news’,'News_PostTime’,'10′,’6′,’News_Passed=1′,’0′ –执行的语句，其中，若where条件为空，也就不传递值