获取总记录数、总页数的存储过程_[Asp.Net教程]
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure Sp_New_ShowType
@shows nvarchar(10),
@Page INT=1,
@PageSize INT=20,
@PageCount INT OUTPUT,
@RecordCount INT OUTPUT
AS
DECLARE @Total INT
DECLARE @strSql varchar(5000)
DECLARE @strSearch varchar(1000)
SET @PageCount=0
SET @RecordCount=0
--获取总记录数
if len(@shows)=1
select @Total=count(id) from Allenterprise where left(newtype,1)=@shows
else if len(@shows)=3
select @Total=count(id) from Allenterprise where left(newtype,3)=@shows
else if len(@shows)=4
select @Total=count(id) from Allenterprise where left(newtype,4)=@shows
SET @RecordCount=@Total
--over
--获取总页数
SET @PageCount=CEILING(CAST(@Total AS float)/@PageSize)
--over
-------------------------------------------------------------------
if @Page<1
set @Page=1
if @Total<>0
begin
if @Page>@pagecount
set @Page=@pagecount
end
--get Data
IF len(@shows)=1
SET @strSearch=’left(newtype,1)=’’’+@shows+’’’’
ELSE IF len(@shows)=3
SET @strSearch=’left(newtype,3)=’’’+@shows+’’’’
Else if len(@shows)=4
SET @strSearch=’left(newtype,4)=’’’+@shows+’’’’
SET @strSql=’SELECT TOP ’ + CAST(@PageSize as varchar) + ’ id,cname FROM Allenterprise WHERE ID not in (
SELECT TOP ’ + CAST((@Page-1) * @PageSize AS varchar) + ’ ID FROM Allenterprise WHERE ’
+ @strSearch + ’order by id desc ) AND ’ + @strSearch+’ order by id desc ’
exec(@strSql)
RETURN @@ERROR
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO