reate PROCEDURE Sp_Conn_Sort
(
@tblName varchar() 表名
@strGetFields varchar() = * 需要返回的列
@fldName varchar()= 排序的字段名
@PageSize int = 页尺寸
@PageIndex int = 页码
@doCount bit = 返回记录总数 非 值则返回
@OrderType bit = 设置排序类型 非 值则降序
@strWhere varchar()= 查询条件 (注意: 不要加 where)
)
AS
declare @strSQL varchar() 主语句
declare @strTmp varchar() 临时变量
declare @strOrder varchar() 排序类型
if @doCount !=
begin
if @strWhere !=
set @strSQL = select count(*) as Total from + @tblName + where +@strWhere
else
set @strSQL = select count(*) as Total from + @tblName
end
以上代码的意思是如果@doCount传递过来的不是就执行总数统计以下的所有代码都是@doCount为的情况
else
begin
if @OrderType !=
begin
set @strTmp = <(select min
set @strOrder = order by + @fldName + desc
如果@OrderType不是就执行降序这句很重要！
end
else
begin
set @strTmp = >(select max
set @strOrder = order by + @fldName + asc
end
if @PageIndex =
begin
if @strWhere !=
set @strSQL = select top + str(@PageSize) + +@strGetFields+ from + @tblName + where + @strWhere + + @strOrder
else
set @strSQL = select top + str(@PageSize) + +@strGetFields+ from + @tblName + + @strOrder
如果是第一页就执行以上代码这样会加快执行速度
end
else
begin
以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = select top + str(@PageSize) + +@strGetFields+ from
+ @tblName + where + @fldName + + @strTmp + (+ @fldName + ) from (select top + str((@PageIndex)*@PageSize) + + @fldName + from + @tblName + + @strOrder + ) as tblTmp)+ @strOrder
if @strWhere !=
set @strSQL = select top + str(@PageSize) + +@strGetFields+ from
+ @tblName + where + @fldName + + @strTmp + (
+ @fldName + ) from (select top + str((@PageIndex)*@PageSize) +
+ @fldName + from + @tblName + where + @strWhere +
+ @strOrder + ) as tblTmp) and + @strWhere + + @strOrder
end
end
exec (@strSQL)
sql server 单主键高效分页存储过程 (支持多字段排序)
Create PROC P_viewPage
/**//*
nzperfect [no_mIss] 高效通用分页存储过程(双向检索) QQ:
敬告适用于单一主键或存在唯一值列的表或视图
ps教程:Sql语句为字节调用时请注意传入参数及sql总长度不要超过指定范围
*/
@TableName VARCHAR() 表名
@FieldList VARCHAR() 显示列名如果是全部字段则为*
@PrimaryKey VARCHAR() 单一主键或唯一值键
@Where VARCHAR() 查询条件 不含where字符如id> and len(userid)>
@Order VARCHAR() 排序 不含order by字符如id ascuserid desc必须指定asc或desc
注意当@SortType=时生效记住一定要在最后加上主键否则会让你比较郁闷
@SortType INT 排序规则 :正序asc :倒序desc :多列排序方法
@RecorderCount INT 记录总数 :会返回总记录
@PageSize INT 每页输出的记录数
@PageIndex INT 当前页数
@TotalCount INT OUTPUT 记返回总记录
@TotalPageCount INT OUTPUT 返回总页数
AS
SET NOCOUNT ON
IF ISNULL(@TotalCount) = SET @TotalCount =
SET @Order = RTRIM(LTRIM(@Order))
SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)) )
WHILE CHARINDEX( @Order) > or CHARINDEX( @Order) >
BEGIN
SET @Order = REPLACE(@Order )
SET @Order = REPLACE(@Order )
END
IF ISNULL(@TableName) = or ISNULL(@FieldList) =
or ISNULL(@PrimaryKey) =
or @SortType < or @SortType >
or @RecorderCount < or @PageSize < or @PageIndex <
BEGIN
PRINT(ERR_)
RETURN
END
IF @SortType =
BEGIN
IF (UPPER(RIGHT(@Order))!= ASC AND UPPER(RIGHT(@Order))!= DESC)
BEGIN PRINT(ERR_) RETURN END
END
DECLARE @new_where VARCHAR()
DECLARE @new_where VARCHAR()
DECLARE @new_order VARCHAR()
DECLARE @new_order VARCHAR()
DECLARE @new_order VARCHAR()
DECLARE @Sql VARCHAR()
DECLARE @SqlCount NVARCHAR()
IF ISNULL(@where) =
BEGIN
SET @new_where =
SET @new_where = Where
END
ELSE
BEGIN
SET @new_where = Where + @where
SET @new_where = Where + @where + AND
END
IF ISNULL(@order) = or @SortType = or @SortType =
BEGIN
IF @SortType =
BEGIN
SET @new_order = orDER BY + @PrimaryKey + ASC
SET @new_order = orDER BY + @PrimaryKey + DESC
END
IF @SortType =
BEGIN
SET @new_order = orDER BY + @PrimaryKey + DESC
SET @new_order = orDER BY + @PrimaryKey + ASC
END
END
ELSE
BEGIN
SET @new_order = orDER BY + @Order
END
IF @SortType = AND CHARINDEX(+@PrimaryKey+ +@Order)>
BEGIN
SET @new_order = orDER BY + @Order
SET @new_order = @Order +
SET @new_order = REPLACE(REPLACE(@new_orderASC{ASC})DESC{DESC})
SET @new_order = REPLACE(REPLACE(@new_order{ASC}DESC){DESC}ASC)
SET @new_order = orDER BY + SUBSTRING(@new_orderLEN(@new_order))
IF @FieldList <> *
BEGIN
SET @new_order = REPLACE(REPLACE(@Order + ASC)DESC)
SET @FieldList = + @FieldList
WHILE CHARINDEX(@new_order)>
BEGIN
IF CHARINDEX(SUBSTRING(+@new_orderCHARINDEX(@new_order))+@FieldList+)>
BEGIN
SET @FieldList =
@FieldList + + SUBSTRING(@new_orderCHARINDEX(@new_order))
END
SET @new_order =
SUBSTRING(@new_orderCHARINDEX(@new_order)+LEN(@new_order))
END
SET @FieldList = SUBSTRING(@FieldListLEN(@FieldList))
END
END
SET @SqlCount = Select @TotalCount=COUNT(*)@TotalPageCount=CEILING((COUNT(*)+)/
+ CAST(@PageSize AS VARCHAR)+) FROM (Select * FROM + @TableName + @new_where+) AS T
IF @RecorderCount =
BEGIN
EXEC SP_EXECUTESQL @SqlCountN@TotalCount INT OUTPUT@TotalPageCount INT OUTPUT
@TotalCount OUTPUT@TotalPageCount OUTPUT
END
ELSE
BEGIN
Select @TotalCount = @RecorderCount
END
IF @PageIndex > CEILING((@TotalCount+)/@PageSize)
BEGIN
SET @PageIndex = CEILING((@TotalCount+)/@PageSize)
END
IF @PageIndex = or @PageIndex >= CEILING((@TotalCount+)/@PageSize)
BEGIN
IF @PageIndex = 返回第一页数据
BEGIN
SET @Sql = Select * FROM (Select TOP + STR(@PageSize) + + @FieldList + FROM
+ @TableName + @new_where + @new_order +) AS TMP + @new_order
END
IF @PageIndex >= CEILING((@TotalCount+)/@PageSize) 返回最后一页数据
BEGIN
SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (
+ Select TOP + STR(ABS(@PageSize*@PageIndex@TotalCount@PageSize))
+ + @FieldList + FROM
+ @TableName + @new_where + @new_order + ) AS TMP
+ @new_order
END
END
ELSE
BEGIN
IF @SortType = wwwpptcom 仅主键正序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+)/@PageSize)/ 正向检索
BEGIN
SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM
+ @TableName + @new_where + @PrimaryKey + >
+ (Select MAX( + @PrimaryKey + ) FROM (Select TOP
+ STR(@PageSize*(@PageIndex)) + + @PrimaryKey
+ FROM + @TableName
+ @new_where + @new_order + ) AS TMP) + @new_order
END
ELSE 反向检索
BEGIN
SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (
+ Select TOP + STR(@PageSize) +
+ @FieldList + FROM
+ @TableName + @new_where + @PrimaryKey + <
+ (Select MIN( + @PrimaryKey + ) FROM (Select TOP
+ STR(@TotalCount@PageSize*@PageIndex) + + @PrimaryKey
+ FROM + @TableName
+ @new_where + @new_order + ) AS TMP) + @new_order
+ ) AS TMP + @new_order
END
END
IF @SortType = 仅主键反序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+)/@PageSize)/ 正向检索
BEGIN
SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM
+ @TableName + @new_where + @PrimaryKey + <
+ (Select MIN( + @PrimaryKey + ) FROM (Select TOP
+ STR(@PageSize*(@PageIndex)) + + @PrimaryKey
+ FROM + @TableName
+ @new_where + @new_order + ) AS TMP) + @new_order
END
ELSE 反向检索
BEGIN
SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (
+ Select TOP + STR(@PageSize) +
+ @FieldList + FROM
+ @TableName + @new_where + @PrimaryKey + >
+ (Select MAX( + @PrimaryKey + ) FROM (Select TOP
+ STR(@TotalCount@PageSize*@PageIndex) + + @PrimaryKey
+ FROM + @TableName
+ @new_where + @new_order + ) AS TMP) + @new_order
+ ) AS TMP + @new_order
END
END
IF @SortType = 多列排序必须包含主键且放置最后否则不处理
BEGIN
IF CHARINDEX( + @PrimaryKey + + @Order) =
BEGIN PRINT(ERR_) RETURN END
IF @PageIndex <= CEILING((@TotalCount+)/@PageSize)/ 正向检索
BEGIN
SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (
+ Select TOP + STR(@PageSize) + + @FieldList + FROM (
+ Select TOP + STR(@PageSize*@PageIndex) + + @FieldList
+ FROM + @TableName + @new_where + @new_order + ) AS TMP
+ @new_order + ) AS TMP + @new_order
END
ELSE 反向检索
BEGIN
SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (
+ Select TOP + STR(@PageSize) + + @FieldList + FROM (
+ Select TOP + STR(@TotalCount@PageSize *@PageIndex+@PageSize) + + @FieldList
+ FROM + @TableName + @new_where + @new_order + ) AS TMP
+ @new_order + ) AS TMP + @new_order
END
END
END
PRINT(@SQL)
EXEC(@Sql)
