SQL分页查询，临时表办法

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