利用存儲過程分頁

USE[iDB002]

GO

/****** Object:  StoredProcedure [dbo].[PICM_Page]    Script Date: 05/24/2013 14:00:33 ******/

SETANSI_NULLS ON

GO

SETQUOTED_IDENTIFIERON

GO

-- =============================================

-- Author       : PICM

-- Create date  : 2013/04/18

-- Description  :燒錄產能新增

-- =============================================

ALTERPROCEDURE [dbo].[PICM_Page]

 

@SQL           NVARCHAR(4000),

@CurrentPage   int,

@PageSize      int = 10,

@OrderBy       NVARCHAR(400),

@ReturnData  NVARCHAR(50)   OUTPUT

 

AS

BEGIN

    ----初始化----------

    SET @ReturnData='0'

   

    if @CurrentPage= 1

        BEGIN

            exec('select top '+@PageSize+' * from ('+@SQL+') r')

            return

        EnD

    else

        begin

            declare @sqlCountnvarchar(4000)

            declare @sqlReturnnvarchar(4000)

            declare @TotalCountint

            declare @TotalPageint

           

             

            set @sqlCount= 'select @count=count(*) from (' + @SQL+ ') r'

           

            exec sp_executesql@sqlCount,N'@count int out',@TotalCount out

           

            set @TotalPage= Ceiling(@TotalCount*1.0/@PageSize)

             

            if @TotalPage< @CurrentPage

                Set @CurrentPage= @TotalPage

             

            SET @ReturnData=cast(@TotalCountas nvarchar)+'_'+cast(@TotalPageas nvarchar)+'_'+cast(@CurrentPageas nvarchar)

           

            SET @sqlReturn= 'select  *  from (select row_number() OVER ('+@OrderBy+') AS

                 row_number,a.* from ('+@SQL+') a

                 ) r where row_number>'+cast(((@CurrentPage-1)*@PageSize)as nvarchar)+

                 ' and row_number<'+cast((@CurrentPage*@PageSize+1)as nvarchar)+@OrderBy

                 

            exec(@sqlReturn)   

           

            return

        end

       

    IF(@@ERROR<>0)

        BEGIN

            SET @ReturnData='ERR'

            return

        END

END

 

 

using (SqlConnection sqlConn =new SqlConnection(ConnCollection.GetSqlConnStr()))

        {

            sqlConn.Open();

             

            SqlCommand cmd = sqlConn.CreateCommand();

 

            cmd.CommandType =CommandType.StoredProcedure;

            cmd.CommandText ="PICM_Page";

           

            cmd.Parameters.Add("@SQL",SqlDbType.NVarChar,4000).Value =@"select  a.* from HR_Emp_Now a

                 union

                 select  a.* from HR_Emp  a";

 

            cmd.Parameters.Add("@CurrentPage",SqlDbType.Int).Value = 2;

            cmd.Parameters.Add("@PageSize",SqlDbType.Int).Value = 10;

            cmd.Parameters.Add("@OrderBy",SqlDbType.NVarChar,400).Value =" order by empid desc ";

            cmd.Parameters.Add("@ReturnData",SqlDbType.NVarChar, 50).Direction =ParameterDirection.Output;

         

            cmd.ExecuteNonQuery();

            Response.Write(cmd.Parameters["@ReturnData"].Value.ToString());

 

            DataSet ds = newDataSet();

 

            SqlDataAdapter adt = new SqlDataAdapter();

 

            adt.SelectCommand = cmd;

            adt.Fill(ds);

            SqlDataReader dr = cmd.ExecuteReader();

            gv.DataSource = ds.Tables[0];

            gv.DataBind();

                  sqlConn.Close();

            

        }