SqlServer获取存储过程的返回值_sql

概述1.OUPUT参数返回值 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 = @@IDENTITY @H_419_6@1.OUPUT参数返回值

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

@H_419_6@存储过程中获得方法：

DECLARE @o_buyerID intDECLARE @o_ID bigintEXEC [nb_order_insert] @o_buyerID,@o_ID output

@H_419_6@2.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_buyerID ))BEGIN INSERT INTO [Order](o_buyerID ) VALUES (@o_buyerID )  SET @o_ID = @@IDENTITY  RETURN 1 — 插入成功返回1 END ELSE  RETURN 0 — 插入失败返回0 END

@H_419_6@存储过程中的获取方法

DECLARE @o_buyerID intDECLARE @o_ID bigintDECLARE @result bitEXEC @result = [nb_order_insert] @o_buyerID,o_ID output

@H_419_6@3.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

@H_419_6@存储过程中的获取方法

@H_419_6@（1）、使用临时表的方法

@H_419_6@

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] — 删除临时表

@H_419_6@1.获取Return返回值

sqlConnection conn = new sqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());conn.open();sqlCommand MyCommand = new sqlCommand("nb_order",conn);  //存储过程名字MyCommand.CommandType = 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()); //取得return的返回值

@H_419_6@2.获取Output输出参数值

sqlConnection conn = new sqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());conn.open();sqlCommand MyCommand = new sqlCommand("nb_order",conn);MyCommand.CommandType = 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()); //指定取得存储过程的返回值

@H_419_6@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;   //指定存储过程  AdDWithValue可以指定名称和值，而Add需要指定名称，类型，再给value         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;    //取得return的值         }         catch (sqlException ex)         {             throw ex;         }         finally         {             conn.Close();         }         return iRet;     }

@H_419_6@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.Decimal).Direction=ParameterDirection.Output;  //利用Add方法为其添加名称，类型和输出参数 try        {            conn.open();            cmd.ExecuteNonquery();            iRet = (decimal)cmd.Parameters["@Amount"].Value;  //取得存储过程中的输出参数        }        catch (sqlException ex)        {            throw ex;        }        finally        {            conn.Close();        }        return iRet;    }

@H_419_6@C#取得结果集：

string sqlw = string.Format("exec sp_UserInfo {0}",uID);            Datatable dsuser = sqlConn.GetDataSet(sqlw).tables[0]; public static DataSet GetDataSet(string sql)        {            string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"].ToString();            sqlConnection conn = new sqlConnection(connStr);            sqlCommand cmd = new sqlCommand(sql,conn);            sqlDataAdapter da = new sqlDataAdapter(cmd);  //直接用sqlDataAdapter将结果集取出来放入dataset中            DataSet ds = new DataSet();            da.Fill(ds);            conn.Close();            cmd.dispose();            return ds;        }