Go homepage(回首页) Upload pictures (上传图片) Write articles (发文字帖)
The author:(作者)归海一刀published in(发表于) 2014/1/30 1:57:49 获取总记录数、总页数的存储过程_[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
赞