Sql中Output参数用法和分页存储过程

USE [CapitalFortune]
GO
/****** Object:  StoredProcedure [dbo].[SetMultiPages]    Script Date: 05/30/2012 10:17:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  hugejile
-- alter date: 2070-9-29
-- Description: 分页,请勿随意改动
-- =============================================
ALTER PROCEDURE [dbo].[SetMultiPages]
 -- Add the parameters for the stored procedure here
  @TableName NVARCHAR(2000),
  @ColumnNames NVARCHAR(2000),
  @Conditions NVARCHAR(2000),
  @OrderbyString NVARCHAR(2000),
  @PageSize INT=1000,
  @CurrentPage INT=1,
  @RecordCount INT=0 OUTPUT,  
  @PageCount INT=1 OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets FROM
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 
 DECLARE @Sql NVARCHAR(4000),
   @RowCount INT
 SET @RowCount=0
 IF @ColumnNames is null or LEN(@ColumnNames)=0
 BEGIN
  SET @ColumnNames='*'
 END

 IF @CurrentPage is null
 BEGIN
  SET @CurrentPage=1
 END
 
 IF @PageSize is null
 BEGIN
  SET @PageSize=10
 END
 
 IF @Conditions is null
 BEGIN
  SET @Conditions=''
 END
 ELSE
 BEGIN
  IF LEN(@Conditions)<>0
  BEGIN
   SET @Conditions=' WHERE ' + @Conditions
  END
 END

 
    -- INSERT statements for procedure here
    IF @CurrentPage>0
    BEGIN
  SET @Sql= N'SELECT * FROM (SELECT '+@ColumnNames+', (ROW_NUMBER() over (ORDER BY '+@OrderbyString+')) AS RowNum FROM '+@TableName+@Conditions+' )T WHERE T.RowNum Between '+Cast((@CurrentPage-1)*@PageSize+1 AS NVARCHAR(10)) + ' and ' + Cast(@CurrentPage*@PageSize AS NVARCHAR(10)) + ';SET @RowCount =ROWCOUNT_BIG()'
  PRINT 'SELECT CommandText: ' + @Sql
  EXECUTE sp_executesql @Sql,N'@RowCount INT OUTPUT',@RowCount OUTPUT 
  
  SET @Sql = 'SELECT @RecordCount = COUNT(*) FROM '+@TableName+@Conditions
  PRINT 'Count CommandText: ' + @Sql
  EXECUTE sp_executesql @Sql,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT 
  SET @PageCount=ceiling(1.0 * @RecordCount / @PageSize)
  PRINT @RecordCount
  --RETURN @RecordCount
 END
 ELSE IF @PageSize>0
 BEGIN
  SET @Sql= N'SELECT TOP ' + Cast(@PageSize as nvarchar(10)) + ' ' + @ColumnNames + ', (ROW_NUMBER() over (ORDER BY '+@OrderbyString+')) AS RowNum FROM '+@TableName+@Conditions+' ORDER BY ' + @OrderbyString
  PRINT 'SELECT CommandText: ' + @Sql
  EXEC (@Sql)
  SET @Sql = 'SELECT @RecordCount = COUNT(*) FROM '+@TableName+@Conditions
  PRINT 'Count CommandText: ' + @Sql
  EXECUTE sp_executesql @Sql,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT 
  SET @PageCount = CEILING(CAST(@RecordCount AS FLOAT)/CAST(@PageSize AS FLOAT))
  --print cast(@RecordCount as nvarchar(10)) + ' pagesize ' + Cast(@PageSize as nvarchar(10))
  --print @PageCount
  --RETURN @RowCount
 END
 ELSE
 BEGIN
  SET @Sql= N'SELECT ' + @ColumnNames + ', (ROW_NUMBER() over (ORDER BY '+@OrderbyString+')) AS RowNum FROM '+@TableName+@Conditions+' ORDER BY ' + @OrderbyString +'; SET @RecordCount = ROWCOUNT_BIG()'
  PRINT 'SELECT CommandText: ' + @Sql
  --EXEC (@Sql)
  --SET @Sql = ''
  --PRINT 'Count CommandText: ' + @Sql
  EXECUTE sp_executesql @Sql,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT 
  SET @PageCount = -1 
 END
 RETURN 1
END

这个是分页存储过程，百万级数据应该没有问题

调用方法

DECLARE @return_value INT,
  @RecordCount INT ,
  @PageCount INT ,
  @PageSize INT

EXEC @return_value = [dbo].[SetMultiPages]
   @TableName = 'USERS',
   @ColumnNames = '*',
   @Conditions = '',
   @OrderbyString = 'UserId',
   @PageSize = 10,
   @CurrentPage = 0,
   @RecordCount = @RecordCount output,
   @PageCount = @PageCount output

 SELECT @RecordCount AS N'RecordCount',
   @PageCount AS N'PageCount',
   @PageSize AS N'PageSize'

@RecordCount = @RecordCount output,
@PageCount = @PageCount output这两个参数是输出参数，用来接受存储过程中的输出参数的值，计算总数据和总页数。调用的时候必须说明输出参数的标志就是Output关键字标识！