撰写存储过程有感

下面是存储过程：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER  PROCEDURE [dbo].[Sp_Hotel_Order_RecycleList1]
@Page int,
@PageSize int,
@Count int OutPut,
@ordernum nvarchar(50),
@UserMobile nvarchar(50),
@nickname nvarchar(300)
AS
declare @sql nvarchar(1000)
set @sql=''
if @ordernum <>''
begin
set @sql=@sql+' and OrderNum='''+@ordernum+''' '
end
if @UserMobile  <>''
begin
set @sql=@sql+'  and UserMobile like ''%'+@UserMobile+'%'' '
end
if @nickname  <>''
begin
set @sql=@sql+'  and (Men_list like ''%'+@nickname+'%'' or UserRname like ''%'+@nickname+'%'') '
end
declare @abc nvarchar(500)
set @abc=' Select Count(*) From H_Order Where Order_IsDel = 1' +  @sql
exec (@abc)
Declare @TSql varchar(1000)
Declare @EndId int

Set @EndId = (@Page - 1)*@PageSize
If @Page = 1
 Begin
  Set @TSql = 'Select Top '+Cast(@PageSize as varchar)+' OrderNum,HotelId,HotelName,RoomType,UserRname,InTime,OutTime,Order_Time From H_Order Where Order_IsDel = 1 '+@sql+'  Order By Id Desc'
 End
Else
 Begin
  Set @TSql = 'Select Top '+Cast(@PageSize as varchar)+' OrderNum,HotelId,HotelName,RoomType,UserRname,InTime,OutTime,Order_Time From H_Order Where Order_IsDel = 1  '+@sql+' And Id<(Select Min(Id) From (Select Top '+Cast(@EndId as varchar)+' Id From H_Order Where Order_IsDel = 1  '+@sql+'  Order By Id Desc) As Tmp) Order By Id Desc'
 End
Exec(@TSql)

下面是c#调用

public Model.Collections.HotelOrderInfoCollection RecycleList(int page,int pagesize,out int count,string ordernum, string UserMobile,string nickname )
  {
   SqlParameter[] param = new SqlParameter[]
    {
     new SqlParameter("@Page",SqlDbType.Int),
     new SqlParameter("@PageSize",SqlDbType.Int),
     new SqlParameter("@Count",SqlDbType.Int),
    new SqlParameter("@ordernum",SqlDbType.NVarChar ,50),
    new SqlParameter("@UserMobile",SqlDbType.NVarChar ,50),
    new SqlParameter("@nickname",SqlDbType.NVarChar ,300)

    };
   param[0].Value = page;
   param[1].Value = pagesize;
   param[2].Direction = System.Data.ParameterDirection.Output;
            param[3].Value = ordernum;
   param[4].Value = UserMobile;
   param[5].Value = nickname;
   Model.Collections.HotelOrderInfoCollection co = new JsSys.Model.Collections.HotelOrderInfoCollection();

   using(SqlConnection Conn = ICon.Conn)
   {
    SqlDataReader dr = SqlHelper.ExecuteReader(Conn,CommandType.StoredProcedure,"Sp_Hotel_Order_RecycleList1",param);
    if (dr.Read())
    {
     count = (int)dr[0]; //调用返回的第一个数据集 exec @abc的数据
    }
    else
    {
     count = 0;
    }
    dr.NextResult(); //调用返回的第二个数据集 exec @Tsql的数据

    while (dr.Read())
    {
     Model.HotelOrderInfo obj = new JsSys.Model.HotelOrderInfo();
     obj.OrderNum = dr["OrderNum"].ToString();
     obj.UserRname = dr["UserRName"].ToString();
     obj.HotelId  = (int)dr["HotelId"];
     obj.HotelName = dr["HotelName"].ToString();
     obj.RoomType = dr["RoomType"].ToString();
     obj.InTime  = dr["InTime"].ToString();
     obj.OutTime  = dr["OutTime"].ToString();
     obj.OrderTime   = dr["Order_Time"].ToString();

     co.Add(obj);
    }
   }

   return co;
  }
  #endregion