SQL 2000存储过程分页

最新推荐文章于 2025-08-22 11:49:22 发布

转载最新推荐文章于 2025-08-22 11:49:22 发布 · 432 阅读

文章标签：

#sql #存储 #tsql #output #table

SQL 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个使用SQL Server存储过程实现的通用分页查询功能，该过程支持自定义查询语句、显示字段、排序方式等，适用于多种场景。通过灵活配置参数，可以有效地进行数据分页展示。

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

CREATE proc [dbo].[page]

@RecordCount int output,

@ReturnCount bit,

@QueryStr nvarchar(1000)='table1',--表名、视图名、查询语句

@PageSize int=20, --每页的大小(行数)

@PageCurrent int=2, --要显示的页从0开始

@FdShow nvarchar (2000)='*', --要显示的字段列表

@IdentityStr nvarchar (100)='id', --主键

@WhereStr nvarchar (2000)='1=1',

@FdOrder nvarchar(100)='desc' --排序只能取desc或者asc

as

set nocount on

declare

@sql nvarchar(2000)

if @WhereStr = '' begin

set @WhereStr = '1=1'

end

if @ReturnCount=1 begin

declare @tsql nvarchar(200)

set @tsql=N'select @RecordCount = count(*) from ' + @QueryStr + ' where ' + @WhereStr

exec sp_executesql @tsql,N'@RecordCount int output',@RecordCount output

end

if @PageCurrent = 0 begin

set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' ' + @FdOrder

end

else begin

if upper(@FdOrder) = 'DESC' begin

set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '< ( select min(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' desc) as t) order by ' + @IdentityStr + ' desc'

end

else begin

set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '> ( select max(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' asc) as t) order by ' + @IdentityStr + ' asc'

end

end

--print @sql

execute(@sql)

--select @t = datediff(ms,@t1,getdate())---------------------