MSSQL存储过程返回自定义标识

 

 

 

 

 

 

 

 

 

 

 

 

本人开发的开发者技术变现资源聚集地,大家支持下，下面是网址

https://www.baiydu.com

 

 

比如我们要做一个登陆，要求严格的也许要进行很多的判断， 如果这时不用自定义返回变量，就会多写很多的代码判断，多次操作数据库。。。

if  exists(select * from sysyobjects where name='Public_Proc_UserLogin')

drop proc  Public_Proc_UserLogin

go

create proc  Public_Proc_UserLogin
  @Name  varchar(50),
  @Pwd varchar(50),
  @ReValue   varchar(50)  output   //我们需要返回的自定义变量
  as
 
  declare @State varchar(50)
  declare @LoginRt varchar(50)
 
  select @State=Ekd_UserState from  Ekd_UserSheet  where  Ekd_UserName=@Name;
  if(@State='激活')
  begin
       select  @LoginRt= Ekd_UserName from   Ekd_UserSheet where     Ekd_UserName=@Name and   Ekd_UserPwd=@Pwd
          if @LoginRt is not null
          
           set @ReValue='1';
          
           else
        
           set @ReValue='0';                                                
  end
 else
   begin
         set @ReValue='2';
       
   end
   return  @ReValue

对应 ADO.NET方法

        public static string ExecuteQuery(string sConnStr, string sSql, Hashtable args)          

       {                         SqlCommand cmd = null;                     

      try    

      {                        

         cmd = new SqlCommand(sSql);                          

        cmd.Connection = new SqlConnection(sConnStr);                           

      cmd.CommandType=CommandType.StoredProcedure;                            

     cmd.Connection.Open();                                

SqlParameter mypara = cmd.Parameters.AddWithValue("@ReValue", SqlDbType.NVarChar);     

    cmd.Parameters.AddWithValue("@ReValue", SqlDbType.NVarChar);                     

            mypara.Direction=ParameterDirection.ReturnValue;                       

              foreach(DictionaryEntry de in args)        {   

                                      if(de.Value.ToString()!=null &&de.Value.ToString()!="")                  

                       {                                        

            cmd.Parameters.AddWithValue("@"+de.Key.ToString(),de.Value.ToString());                           

       }                                 

     }                                                           

   cmd.ExecuteNonQuery();                              

   string reValue = (string)cmd.Parameters["@ReValue"].Value.ToString();           

                      return reValue;                        

}                     

    finally                     

    {                             

    cmd.Connection.Close();                        

         if(cmd != null) cmd.Dispose();                      

   }             

    }

        #endregion     }