非常实用的SQL存储过程分页

ALTER PROCEDURE [dbo].[GetUserQuestionList]

 @PAGENO INT,--页码

@PAGESIZEINT,--页面显示条数

@RETCOUNTINT OUTPUT --返回页面记录总数

AS

BEGIN

     DECLARE @TMP_SQL  NVARCHAR(4000),@TMP_SQL1  NVARCHAR(4000)

    SET @TMP_SQL='SELECT * from TABLENAME'

    SET @TMP_SQL1='SELECT @A=COUNT(*)  FROM TABLENAME'

    SET @TMP_SQL='SELECT * FROM   (SELECT ROW_NUMBER() OVER (order by id DESC)as RowNumber,* FROM ('+@TMP_SQL+' ) T1) T                   WHERE T.RowNumber>='+CAST(@PAGESIZE*(@PAGENO-1)+1 AS VARCHAR(10))+' AND                          T.RowNumber<='+CAST(@PAGESIZE* @PAGENO      AS VARCHAR(10))  

   --print @TMP_SQL

   --print @TMP_SQL1

   EXEC(@TMP_SQL)

   DECLARE @TMP VARCHAR(10)

   exec sp_executesql @TMP_SQL1,N'@a VARCHAR(10) output',@TMPoutput SET @RETCOUNT=CAST(@TMP AS INT) 

   --print @RETCOUNTEND

END