存储过程写法.doc

获取存储过程返回值及代码中获取返回值1.OUPUT参数返回值例： 向Order表插入一条记录,返回其标识CREATEPROCEDUREdbo.nb_order_insert(o_buyeridint,o_idbigintOUTPUT)ASBEGINSETNOCOUNTON;BEGININSERTINTOOrder(o_buyerid)VALUES(o_buyerid)SETo_id=IDENTITYENDEND存储过程中获得方法：DECLAREo_buyeridintDECLAREo_idbigintEXECnb_order_inserto_buyerid,o_idbigint2.RETURN过程返回值CREATEPROCEDUREdbo.nb_order_insert(o_buyeridint,o_idbigintOUTPUT)ASBEGINSETNOCOUNTON;IF(EXISTS(SELECT*FROMShopWHEREs_id=o_shopid)BEGININSERTINTOOrder(o_buyerid)VALUES(o_buyerid)SETo_id=IDENTITYRETURN1插入成功返回1ENDELSERETURN0插入失败返回0END存储过程中的获取方法DECLAREo_buyeridintDECLAREo_idbigintDECLAREresultbitEXECresult=nb_order_inserto_buyerid,o_idbigint3.SELECT 数据集返回值CREATEPROCEDUREdbo.nb_order_select(o_idint)ASBEGINSETNOCOUNTON;SELECTo_id,o_buyeridFROMOrderWHEREo_id=o_idGO存储过程中的获取方法（1）、使用临时表的方法CREATETABLEdbo.Temp(o_idbigintIDENTITY(1,1)NOTFORREPLICATIONNOTNULL,o_buyeridintNOTNULL)INSERTTempEXECnb_order_selecto_id这时Temp就是EXEC执行SELECT后的结果集SELECT*FROMTempDROPTemp删除临时表（2）、速度不怎么样.（不推荐）SELECT*fromopenrowset(provider_name,Trusted_Connection=yes,execnb_order_select)1.获取Return返回值程序代码/存储过程/Create PROCEDURE MYSQL/ a int,/ b int/AS/ return a + b/GOSqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStringsLocalSqlServer.ToString();conn.Open();SqlCommand MyCommand = new SqlCommand(MYSQL, conn);MyCommand.CommandType = CommandType.StoredProcedure;MyCommand.Parameters.Add(new SqlParameter(a, SqlDbType.Int);MyCommand.Parametersa.Value = 10;MyCommand.Parameters.Add(new SqlParameter(b, SqlDbType.Int);MyCommand.Parametersb.Value = 20;MyCommand.Parameters.Add(new SqlParameter(return, SqlDbType.Int);MyCommand.Parametersreturn.Direction = ParameterDirection.ReturnValue;MyCommand.ExecuteNonQuery();Response.Write(MyCommand.Parametersreturn.Value.ToString();2.获取Output输出参数值程序代码/存储过程/Create PROCEDURE MYSQL/ a int,/ b int,/ c int output/AS/ Set c = a + b/GOSqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStringsLocalSqlServer.ToString();conn.Open();SqlCommand MyCommand = new SqlCommand(MYSQL, conn);MyCommand.CommandType = CommandType.StoredProcedure;MyCommand.Parameters.Add(new SqlParameter(a, SqlDbType.Int);MyCommand.Parametersa.Value = 20;MyCommand.Parameters.Add(new SqlParameter(b, SqlDbType.Int);MyCommand.Parametersb.Value = 20;MyCommand.Parameters.Add(new SqlParameter(c, SqlDbType.Int);MyCommand.Parametersc.Direction = ParameterDirection.Output;MyCommand.ExecuteNonQuery();Response.Write(MyCommand.Parametersc.Value.ToString();C#接收存储过程返回值: public static int User_Add(User us) int iRet; SqlConnection conn = new SqlConnection(Conn_Str); SqlCommand cmd = new SqlCommand(User_Add, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(UName, us.UName); cmd.Parameters.AddWithValue(UPass, us.UPass); cmd.Parameters.AddWithValue(PassQuestion, us.PassQuestion); cmd.Parameters.AddWithValue(PassKey, us.PassKey); cmd.Parameters.AddWithValue(Email, us.Email); cmd.Parameters.AddWithValue(RName, us.RName); cmd.Parameters.AddWithValue(Area, us.Area); cmd.Parameters.AddWithValue(Address, us.Address); cmd.Parameters.AddWithValue(ZipCodes, us.ZipCodes); cmd.Parameters.AddWithValue(Phone, us.Phone); cmd.Parameters.AddWithValue(QQ, us.QQ); cmd.Parameters.Add(RETURN_VALUE, ).Direction = ParameterDirection.ReturnValue; try conn.Open(); cmd.ExecuteNonQuery(); iRet = (int)cmd.ParametersRETURN_VALUE.Value; catch (SqlException ex) throw ex; finally conn.Close(); return iRet; C#接收存储过程输出参数: public static decimal Cart_UserAmount(int UID) decimal iRet; SqlConnection conn = new SqlConnection(Conn_Str); SqlCommand cmd = new SqlCommand(Cart_UserAmount, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(UID, UID);cmd.Parameters.Add(Amount, SqlDbType.De