获取存储过程返回值及代码中获取返回值
来源:互联网 发布:linux vi进入编辑模式 编辑:程序博客网 时间:2024/06/11 18:26
例: 向Order表插入一条记录,返回其标识
@o_buyerid int ,
@o_id bigint OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN
INSERT INTO [Order](o_buyerid )
VALUES (@o_buyerid )
SET @o_id = @@IDENTITY
END
END
存储过程中获得方法:
DECLARE @o_id bigint
EXEC [nb_order_insert] @o_buyerid ,o_id bigint
@o_buyerid int ,
@o_id bigint OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
IF(EXISTS(SELECT * FROM [Shop] WHERE [s_id] = @o_shopid))
BEGIN
INSERT INTO [Order](o_buyerid )
VALUES (@o_buyerid )
SET @o_id = @@IDENTITY
RETURN 1 — 插入成功返回1
END
ELSE
RETURN 0 — 插入失败返回0
END
存储过程中的获取方法
DECLARE @o_id bigint
DECLARE @result bit
EXEC @result = [nb_order_insert] @o_buyerid ,o_id bigint
3.SELECT 数据集返回值
@o_id int
)
AS
BEGIN
SET NOCOUNT ON;
SELECT o_id,o_buyerid FROM [Order]
WHERE o_id = @o_id
GO
4. 存储过程中的获取方法
(1)、使用临时表的方法
[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] — 删除临时表
.NET 获取存储过程返回值
1.获取Return返回值
//存储过程//Create PROCEDURE MYSQL// @a int,// @b int//AS// return @a + @b//GOSqlConnection 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输出参数值
//存储过程//Create PROCEDURE MYSQL// @a int,// @b int,// @c int output//AS// Set @c = @a + @b//GOSqlConnection 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 = 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;
}
- 存储过程返回值及代码中获取返回值
- 获取存储过程返回值及代码中获取返回值
- 获取存储过程返回值及代码中获取返回值
- 获取存储过程返回值及代码中获取返回值
- 获取存储过程返回值及代码中获取返回值
- 获取存储过程返回值及代码中获取返回值
- 获取存储过程返回值及代码中获取返回值
- 获取存储过程返回值
- Java代码获取oracle存储过程返回值
- mybatis调用存储过程及返回值获取
- c#中获取存储过程的返回值(return Value)
- 获取存储过程的返回值,用于其他变量中
- PB中调用存储过程获取返回值
- EF中执行存储过程,获取output返回值
- .net存储过程,获取返回值
- 获取存储过程的返回值
- 获取存储过程 Return 返回值
- C#获取存储过程返回值
- asp.net常用代码(1)
- 静态注册和动态注册总结
- 怎样在Linux中查看apache是用那个httpd.conf
- POJ 2478 Farey Sequence(Farey数列&欧拉函数求和)
- linux下使用expect实现自动化脚本
- 获取存储过程返回值及代码中获取返回值
- asp.net常用代码(2)
- WebSphere使用 Administration Thin Client
- asp.net常用代码(3)
- POJ 1789
- hdu 4490 Mad Veterinarian ( BFS+链表记录路径 )
- iOS Framework: Introducing MKNetworkKit (MKNetworkKit介绍,入门,翻译)
- 开始新的博客
- Ubuntu 12.04 成功编译安装 GCC 4.8.1