存储过程写法

获取存储过程返回值及代码中获取返回值1.OUPUT参数返回值例：向Order表插入一条记录,返回其标识CREATE PROCEDURE[dbo].[nb_order_insert](@o_buyerid int ,@o_id bigint OUTPUT)ASBEGINSET NOCOUNT ON;BEGININSERT INTO[Order](o_buyerid )VALUES (@o_buyerid )SET@o_id=@@IDENTITYENDEND存储过程中获得方法：DECLARE@o_buyerid intDECLARE@o_id bigintEXEC[nb_order_insert]@o_buyerid ,o_id bigint2.RETURN过程返回值CREATE PROCEDURE[dbo].[nb_order_insert](@o_buyerid int ,@o_id bigint OUTPUT)ASBEGINSET NOCOUNT ON;IF(EXISTS(SELECT*FROM[Shop]WHERE[s_id]=@o_shopid))BEGININSERT INTO[Order](o_buyerid )VALUES (@o_buyerid )SET@o_id=@@IDENTITYRETURN1—插入成功返回1ENDELSERETURN0—插入失败返回0END存储过程中的获取方法DECLARE@o_buyerid intDECLARE@o_id bigintDECLARE@result bitEXEC@result=[nb_order_insert]@o_buyerid ,o_id bigint3.SELECT 数据集返回值CREATE PROCEDURE[dbo].[nb_order_select](@o_id int)ASBEGINSET NOCOUNT ON;SELECT o_id,o_buyerid FROM[Order]WHERE o_id =@o_idGO存储过程中的获取方法（1）、使用临时表的方法CREATE TABLE[dbo].[Temp]([o_id][bigint]IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,[o_buyerid][int]NOT NULL)INSERT[Temp]EXEC[nb_order_select]@o_id–这时Temp就是EXEC执行SELECT 后的结果集SELECT*FROM[Temp]DROP[Temp]—删除临时表（2）、速度不怎么样.（不推荐）SELECT*from openrowset(’provider_name','Trusted_Connection=yes’,'exec nb _order_select’)1.获取Return返回值程序代码//存储过程//Create PROCEDURE MYSQL// @a int,// @b int//AS// return @a + @b//GOSqlConnection conn = newSqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToStrin g());conn.Open();SqlCommand MyCommand = new SqlCommand("MYSQL", conn); mandType = CommandType.StoredProcedure; MyCommand.Parameters.Add(new SqlParameter("@a", SqlDbType.Int)); MyCommand.Parameters["@a"].Value = 10;MyCommand.Parameters.Add(new SqlParameter("@b", SqlDbType.Int)); MyCommand.Parameters["@b"].Value = 20;MyCommand.Parameters.Add(new SqlParameter("@return", SqlDbType.Int)); MyCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue; MyCommand.ExecuteNonQuery();Response.Write(MyCommand.Parameters["@return"].Value.ToString());2.获取Output输出参数值程序代码//存储过程//Create PROCEDURE MYSQL// @a int,// @b int,// @c int output//AS// Set @c = @a + @b//GOSqlConnection conn = newSqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToStrin g());conn.Open();SqlCommand MyCommand = new SqlCommand("MYSQL", conn); mandType = CommandType.StoredProcedure; MyCommand.Parameters.Add(new SqlParameter("@a", SqlDbType.Int)); MyCommand.Parameters["@a"].Value = 20;MyCommand.Parameters.Add(new SqlParameter("@b", SqlDbType.Int)); MyCommand.Parameters["@b"].Value = 20;MyCommand.Parameters.Add(new SqlParameter("@c", SqlDbType.Int)); MyCommand.Parameters["@c"].Direction = ParameterDirection.Output; MyCommand.ExecuteNonQuery();Response.Write(MyCommand.Parameters["@c"].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);mandType = 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.Parameters["@RETURN_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);mandType = CommandType.StoredProcedure;cmd.Parameters.AddWithValue("@UID", UID);cmd.Parameters.Add("@Amount",SqlDbType.Decimal).Direction=ParameterDirection.Output;try{conn.Open();cmd.ExecuteNonQuery();iRet = (decimal)cmd.Parameters["@Amount"].Value;}catch (SqlException ex){throw ex;}finally{conn.Close();}return iRet;}。