我的第一个存储过程!

1.存储过程的创建:+++++++++++++++++++++++++++++++++

/*++++++++++++功 能：根据不同的条件来统计Count(*)记录条数+++++++++++

--EXECUTE Count_info 1,'',620000,GanSu

--EXECUTE Count_info 2,'',620000,GanSu

--EXECUTE Count_info 3,'',620000,GanSu

--EXECUTE Count_info 3,'565',620000,GanSu

++++++++++++作 者：Esoutong[我的第一个存储过程哈哈] ++++++++++++
*/

CREATE PROC Count_info( @Flag INT, @Type varchar(50), @ID INT ,@Table varchar(20) )

AS
DECLARE @SQL VARCHAR(2000)
if @Flag=1
    BEGIN
  SELECT @SQL='select  Count(*) from '+ @Table +' where AreaDigit between '+ CONVERT (VARCHAR(20),@ID) +' and '+ CONVERT (VARCHAR(20),@ID+9999) 
  
    END 
 
if @Flag=2
    BEGIN
  SELECT @SQL='select  Count(*) from '+ @Table +' where AreaDigit between '+ CONVERT (VARCHAR(20),@ID) +' and '+ CONVERT (VARCHAR(20),@ID+99) 
  
    END 

if @Flag=3
    BEGIN
                if @Type=' '
                   Begin
       SELECT @SQL='select  Count(*) from '+ @Table +' where AreaDigit='+ CONVERT (VARCHAR(20),@ID)   
                   END
                else
                   Begin
       SELECT @SQL='select  Count(*) from '+ @Table +' where EconomyWay='''+CONVERT (VARCHAR(20),@Type)+'''and AreaDigit='+ CONVERT (VARCHAR(20),@ID)  
                   END                  
                              
    END
exec(@sql)
GO

2.存储过程的调用方法:+++++++++++++++++++++++++++++++++

/// <summary>
  /// CountReco：调用SQL存储过程Count_info
  /// </summary>
  /// <param name="ProName"></param>
  /// <param name="Flag"></param>
  /// <param name="Type"></param>
  /// <param name="ID"></param>
  /// <param name="Table"></param>
  /// <returns></returns>
  public string CountReco(string ProName,int Flag,string Type,int ID,string Table)
  {
   string Count="";
   conn=new SqlConnection(esoutong.constr);
   cmd=new SqlCommand(ProName, conn);
   conn.Open();

   cmd.CommandType = CommandType.StoredProcedure;

   SqlParameter parameterFlag = new SqlParameter("@Flag", SqlDbType.Int, 4);
   parameterFlag.Value = Flag;
   cmd.Parameters.Add(parameterFlag);

   SqlParameter parameterType= new SqlParameter("@Type", SqlDbType.NChar, 50);
   parameterType.Value = Type;
   cmd.Parameters.Add(parameterType);

   SqlParameter parameterID= new SqlParameter("@ID", SqlDbType.Int, 4);
   parameterID.Value = ID;
   cmd.Parameters.Add(parameterID);

   SqlParameter parameterTable= new SqlParameter("@Table", SqlDbType.NChar, 20);
   parameterTable.Value = Table;
   cmd.Parameters.Add(parameterTable);

   
   SqlDataReader rdr=cmd.ExecuteReader();
   if(rdr.Read())
   {
    Count=rdr[0].ToString();

   }
      rdr.Close();
   conn.Close();
   
   return Count;
  }

3.ASP.NET中的调用:CountReco("Count_info",1,"",620000,"GanSu")