存储过程分页

最新推荐文章于 2020-11-19 10:17:22 发布

原创最新推荐文章于 2020-11-19 10:17:22 发布 · 376 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#存储 #sql

SQL Server 专栏收录该内容

5 篇文章

订阅专栏

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[GetDataByPager2]
   @startIndex INT,--起始记录数，从0开始
   @tableName VARCHAR(50),
   @pageSize INT=5,
   @condition VARCHAR(1000)='1=1',
   @key VARCHAR(20)='id',
   @orderType VARCHAR(20)='desc'
AS
BEGIN
DECLARE @TopCount INT
--SET @TopCount=(@pageIndex-1)*@pageSize
DECLARE @SQL VARCHAR(1000)
SET @SQL='select TOP ' +CONVERT(VARCHAR(20),@pagesize)+' * FROM '+@tableName
           +' WHERE '+ @condition+' and '+@key+' NOT IN(SELECT TOP ' +CONVERT(VARCHAR(20),@startIndex)+' '+@key+' FROM '+@tableName+' where '+@condition+' order by '+@key+' '+@orderType+') order by '+@key+' '+@orderType
--SET @SQL =@SQL+' SELECT COUNT(*) FROM '+@TableName+' WHERE '+@condition
PRINT(@SQL)
EXEC(@SQL)
END