多字段排序分页(存储过程)

最新推荐文章于 2024-08-15 18:52:23 发布

转载最新推荐文章于 2024-08-15 18:52:23 发布 · 829 阅读

文章标签：

#存储 #output

asp.net 同时被 2 个专栏收录

10 篇文章

订阅专栏

SQL Server

6 篇文章

订阅专栏

本文介绍了一个SQL Server中用于实现高效分页查询的存储过程。该过程支持自定义表名、字段、排序方式等参数，并能根据输入的页码和每页显示数量返回相应数据。此外，还提供获取总记录数及总页数的功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE PROCEDURE [dbo].[Pager_orderid] 

( 

@sTable nvarchar(100), --表名

@sPkey nvarchar(50),            --主键(一定要有) 

@sField nvarchar(1000)='*',     --字段

@iPageCurr int,                 --当前页数

@iPageSize int,                 --每页记录数

@sCondition nvarchar(1000),  --条件(不需要where) 

@sOrder nvarchar(100) ,         --排序(不需要order by,需要asc和desc字符) 

@Counts int=0 output,           --记录条数(已有值:外部赋值,0执行count) 

@pageCount int=1 output     --查询结果分页后的总页数

) 

AS 

SET NOCOUNT ON 

DECLARE @Recordcount varchar(5000) --总记录数

DECLARE @sC1 nvarchar(1000),@sC2 nvarchar(1000) 

DECLARE @iAsc int,@iDesc int,@iType tinyint 

DECLARE @sT1 nvarchar(1000),@sT2 nvarchar(1000),@sT3 nvarchar(1000),@sT4 nvarchar(1000),@sSQL nvarchar(4000) 

/*----------------------判断where 条件是否空值-------------------*/ 

IF LEN(@sCondition)>2 

 begin

 SELECT @sC1=' where '+@sCondition+' ', @sC2=' where '+@sCondition+' and ' 

 set @Recordcount= 'select count(1) as Total from[' + @sTable + '] where ' + @sCondition

 end

 ELSE 

 BEGIN 

  SELECT @sC1=' ', @sC2=' where ' 

 END 



SELECT @sT1=UPPER(@sOrder), @sT2=@sOrder, @iType=0, @sOrder=' ', @sT4=UPPER(@sPkey) 

/*-----------------------获取查询的数据行数---------------------*/ 

IF LEN(@sT2)>2 

BEGIN 

 SELECT @iAsc=0, @iDesc=0 

 IF @sT4=SUBSTRING(@sT1,0,LEN(@sT4)) --存在主建

     BEGIN 

  SELECT @iAsc=CHARINDEX('ASC',@sT1), @iDesc=CHARINDEX('DESC',@sT1) 

 END 

 IF (@iAsc>0 and @iDesc=0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc<@iDesc)) 

  SELECT @iType=1, @sT3='>(SELECT MAX(' 

 ELSE IF (@iAsc=0 and @iDesc>0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc>@iDesc)) 

 

 BEGIN 

  SELECT @iType=1, @sT3='<(SELECT MIN('

 END 

 SET @sOrder='ORDER BY '+@sT2 

END 

/*-------------------------获取查询的数据行数----------------------*/ 

--IF (@Counts<1) 

--BEGIN 

 --SET @sSQL=’SELECT @Counts=Count(0) FROM ’+@sTable+@sC1 

 --EXEC sp_executesql @sSQL,N’@Counts int OUT’,@Counts OUT  

--END 

SET @sSQL='SELECT @Counts=Count(0) FROM '+@sTable+@sC1 

EXEC sp_executesql @sSQL,N'@Counts int OUT',@Counts OUT 

SET @pageCount=(@Counts+@ipageSize-1)/@ipageSize 

IF @iPageCurr>@pageCount 

 SET @iPageCurr=@pageCount 

SELECT @iPageCurr=(CASE WHEN @Counts<(@iPageCurr-1)*@iPageSize THEN CEILING(@Counts/@iPageSize) WHEN @iPageCurr<1 THEN 1 ELSE @iPageCurr END) 

IF (@iPageCurr>1) AND (@iType=1) 

 SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC2+@sPkey+@sT3+@sPkey+') FROM (SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+') AS tbTemp)'+@sOrder 

ELSE IF (@iPageCurr>1) AND (@iType=0) 

 SET @sSQL='SELECT '+@sField+' FROM '+@sTable+@sC2+@sPkey+' IN (SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC2+@sPkey+' NOT IN(SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+')'+@sOrder+')'+@sOrder 

ELSE 

BEGIN 

    SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC1+@sOrder 

END 

EXEC(@sSQL) 

EXEC(@Recordcount)

SET NOCOUNT OFF