sqlserver获取存储过程返回值

发布时间：2020-12-12 14:03:38 所属栏目：MsSql教程来源：网络整理

导读：1.OUPUT参数返回值 [sql] ? view plain copy CREATE ? PROCEDURE ?[dbo].[nb_order_insert](?? @o_buyerid? int ?,?? @o_id? bigint ? OUTPUT ?? )?? AS ?? BEGIN ?? SET ?NOCOUNT? ON ;?? INSERT ? INTO ?[ Order ](o_buyerid?)?? VALUES ?(@o_buyerid?)??

1.OUPUT参数返回值

[sql]? view plain copy

CREATE?PROCEDURE?[dbo].[nb_order_insert](??
@o_buyerid?int?,??
@o_id?bigint?OUTPUT??
)??
AS??
BEGIN??
SET?NOCOUNT?ON;??
INSERT?INTO?[Order](o_buyerid?)??
VALUES?(@o_buyerid?)??
SET?@o_id?=?@@IDENTITY??
END??
END??

存储过程中获得方法：

DECLARE ? @o_buyerid int
@o_id bigint EXEC ? [ nb_order_insert ] ,@o_id? output
2.RETURN过程返回值

CREATE?PROCEDURE?[dbo].nb_order_insert(
@o_buyeridint?,
@o_idbigint?OUTPUT
)
AS

BEGIN

SET?NOCOUNT?ON;
IF(EXISTS(SELECT?*FROMShopWHEREs_id=@o_shopid))
BEGIN INSERTINTOOrder(o_buyerid?)

VALUES?(?)

?@@IDENTITY RETURN?1?—?插入成功返回1

END ELSE ?0?—?插入失败返回0

END
存储过程中的获取方法

@result bit ? = ?
3.SELECT 数据集返回值

nb_order_selectint

)
AS ?o_id,o_buyerid?] ?o_id?@o_id GO
存储过程中的获取方法
（1）、使用临时表的方法

CREATE TABLE dbo . Temp (

o_id bigint IDENTITY ( 1 )? NOT FOR REPLICATION NULL

o_buyerid int

)

INSERT nb_order_select

–?这时? Temp ?就是EXEC执行SELECT?后的结果集

SELECT * FROM DROP ?—?删除临时表
（2）、速度不怎么样.（不推荐）

from openrowset (’provider_name ' ' Trusted_Connection yes’,0)">exec?nb_order_select’)
1.获取Return返回值

//存储过程
//Create PROCEDURE MYSQL
//???? @a int,
//???? @b int
//AS
//???? return @a + @b
//GO
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
conn.Open();
SqlCommand MyCommand = new SqlCommand("MYSQL",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());

2.获取Output输出参数值

//???? @b int,
//???? @c int output
//AS
//???? Set @c = @a + @b
//GO
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
conn.Open();
SqlCommand MyCommand = new SqlCommand("MYSQL",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);
???????? 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.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); ??????? cmd.CommandType = 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; ??? }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!