我的sql存储过程是执行分页查询的 返回当前页的数据datatable 和output输出参数总条数pagecount能一条sql

给你个我在用的分页存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[global.pagebreak]
@pFields varchar(4000)='*',--查询的字段
@pSortField varchar(2000)='',--排序的字段
@pTable varchar(3000)='',--所查的表
@pWhere varchar(4000)='',--条件
@pOnePageRows int = 25,--每页显示的条数
@pIndex int = 1
as
declare @s varchar(5000)
if (@pWhere='' or @pWhere=NULL)
begin
set @s ='
DECLARE @totalRows int
DECLARE @totalPages int
SELECT @totalRows = COUNT(*) From '+@pTable+'
SET @totalPages = @totalRows / '+cast(@pOnePageRows as varchar)+'
IF( @totalPages * '+cast(@pOnePageRows as varchar) + '< @totalRows)
SELECT @totalPages = @totalPages + 1
SELECT '+@pFields+' FROM(
SELECT ROW_NUMBER() OVER(order by '+@pSortField+') as ROWID, '+@pFields+' From '+@pTable+')
AS TSP
WHERE ROWID BETWEEN '+cast((((@pIndex-1)*@pOnePageRows)+1) as varchar)+'
AND '+cast((@pIndex*@pOnePageRows) as varchar)+'
SELECT @totalRows AS TOTALROWS,
@totalPages AS TOTALPAGES,
'+cast(@pIndex as varchar)+' AS CURRENTPAGE,
'+cast(@pOnePageRows as varchar)+' AS ONEPAGEROWS '
end
else
begin
set @s ='
DECLARE @totalRows int
DECLARE @totalPages int
SELECT @totalRows = COUNT(*) From '+@pTable+' where '+@pWhere+'
SELECT @totalPages = @totalRows / '+cast(@pOnePageRows as varchar)+'
IF( @totalPages * '+cast(@pOnePageRows as varchar) + '< @totalRows)
SELECT @totalPages = @totalPages + 1
SELECT '+@pFields+' FROM(
SELECT ROW_NUMBER() OVER(order by '+@pSortField+') as ROWID, '+@pFields+' From '+@pTable+' where '+@pWhere+')
AS TSP
WHERE ROWID BETWEEN '+cast((((@pIndex-1)*@pOnePageRows)+1) as varchar)+'
AND '+cast((@pIndex*@pOnePageRows) as varchar)+'
SELECT @totalRows AS TOTALROWS,
@totalPages AS TOTALPAGES,
'+cast(@pIndex as varchar)+' AS CURRENTPAGE,
'+cast(@pOnePageRows as varchar)+' AS ONEPAGEROWS '
end
print @s
exec(@s)

你这个存储过程已经够烂了，还要用一句来实现，你这个思路反了，既麻烦又没效率。