-- =============================================
-- Author:        Henson
-- Create date: 2011-06-01
-- Description:    根据查询条件返回符合的条件的数据总数及当页数据
-- =============================================
CREATE PROCEDURE [dbo].[ST_GetPagedData]
    @MbCd varchar(12),--会员编号
    @MbName varchar(20),--会员姓名
    @PageSize int,--每页显示的记录数
    @CurrPage int, --当前页索引
    @RecordCount int output --总记录数(输出参数)
AS
BEGIN
    Declare @strSql varchar(4000)
    Declare @strWhere varchar(1000)
    --根据参数生成Where条件
    Set @strWhere = ''
    If (isnull(@MbCd,'') != '')
    Begin
        Set @strWhere = @strWhere + ' And MB_CD='''+@MbCd+''''
    End
    If (isnull(@MbName,'') != '')
    Begin
        Set @strWhere = @strWhere + ' And Name like ''%'+@MbName+'%'''
    End
    
    
    --获取记录总数
    Declare @strCountSql nvarchar(1000)
    Set @strCountSql = 'Select @RecordCount=count(*) From M_Account a  Where 1=1 '+@strWhere
    exec sp_executesql @strCountSql, N'@RecordCount int output',@RecordCount output
    --print (@strCountSql)
    
    --获取数据
    
    declare @topIdNum int ,@delNum int
    set @topIdNum = @PageSize*@CurrPage
    set @delNum = (@CurrPage-1)*@PageSize
    
    set @strSql = '
        DECLARE @temptb table
        (
            uid int identity(1,1),   
            Mb_Cd varchar(12)
        )
        
        Insert Into @temptb(Mb_Cd) 
        Select Top '+ cast( @topIdNum as varchar(10) )+' a.Mb_Cd,
        From M_Account a
        Where 1=1 '+@strWhere+N'

        delete from @temptb where uid<='+ cast( @delNum as varchar(10) )+N'

        Select *
        From M_Account a 
            Inner Join @temptb b On a.Mb_Cd=b.Mb_Cd
        '
        
    exec (@strSql)
    
END