获取总记录数、总页数的存储过程_[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