SQL分页查询，临时表办法

最新推荐文章于 2023-04-20 07:23:21 发布

弈剑风雨声

最新推荐文章于 2023-04-20 07:23:21 发布

阅读量476

点赞数

分类专栏： SQL SERVER 文章标签： SQL 临时表分页

本文链接：https://blog.csdn.net/qq_24351147/article/details/44457067

版权

SQL SERVER 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure FiltrPager
(
@MotherBID varchar(20)='',
@BloodType varchar(4)='',
@FiltrStartDate Datetime=null,
@FiltrEndDate datetime=null,
@OperID varchar(20)='',
@TotalCount int outPut,
@pageCount int,
@pageIndex int
)
as
declare @minRowNum int
declare @maxRowNum int
begin
set @minRowNum=(@pageIndex-1)*@pageCount+1
set @maxRowNum=@pageIndex*@pageCount
create table #Temp
(
ID int primary key identity(1,1),
RecordID int
)
declare @sql nvarchar(1000)
set @sql=N'insert into #Temp select RecordID from Filtr_BaseInfo where 1=1'
IF (@MotherBID is not null) and (@MotherBID <> '')
BEGIN
set @sql=@sql+' and MotherBID like '+'''%'+@MotherBID+'%'''
END
IF (@BloodType is not null) and (@BloodType <> '')
BEGIN
set @sql=@sql+' and BloodType='+@BloodType
END
IF (@OperID is not null) and (@OperID <> '')
BEGIN
set @sql=@sql+' and OperID='+@OperID
END
if IsNull(@FiltrStartDate,'')<>'1900-01-01 00:00:00.000'
BEGIN
set @sql=@sql+' and FiltrDate>='+char(39)+Convert(varchar(20),@FiltrStartDate,120)+char(39)
END
IF IsNull(@FiltrEndDate,'')<>'1900-01-01 00:00:00.000'
BEGIN
set @sql=@sql+' and FiltrDate<='+char(39)+Convert(varchar(20),@FiltrEndDate,120)+char(39)
END
exec sp_executesql @sql
SET @TotalCount = ( SELECT COUNT(ID) FROM #Temp)
select a.* from Filtr_BaseInfo a,#Temp b where a.RecordID=b.RecordID and b.ID>=@minRowNum and b.ID<=@maxRowNum
drop table #Temp
end