数据库中使用output值和return值:
--
-----------------------------------------------------
-- 数获存储过程中的OUTPUT参数和返回值获取
-- -----------------------------------------------------
CREATE PROCEDURE Proc_Test; 1
@INPUT int,
@OUTPUT int output
AS
BEGIN
SET NOCOUNT ON;
SELECT @OUTPUT = @INPUT
RETURN @INPUT + 1
END
GO
-- 调用output值和return返回值
DECLARE @OUT int, @RETURN int
EXEC @RETURN =Proc_Test; 1
0,
@OUT output
SELECT [ 返回值 ] = @RETURN, [ OUTPUT值 ] = @OUT
返回值 OUTPUT值
-- --------- -----------
1 0
-- ---------------------------------------------------
-- SP_EXECUTESQL中的OUTPUT参数获取
-- ---------------------------------------------------
DECLARE @Para1 int, @Para2 int, @SUM int
EXECUTE SP_EXECUTESQL
N ' SELECT @SUM=@Para1+@Para2 ',
N ' @Para1 INT,@Para2 INT,@SUM INT OUTPUT ',
5, 5, @SUM OUTPUT
SELECT [ OUTPUT值 ] = @SUM
OUTPUT值
-- ---------
10
-- 数获存储过程中的OUTPUT参数和返回值获取
-- -----------------------------------------------------
CREATE PROCEDURE Proc_Test; 1
@INPUT int,
@OUTPUT int output
AS
BEGIN
SET NOCOUNT ON;
SELECT @OUTPUT = @INPUT
RETURN @INPUT + 1
END
GO
-- 调用output值和return返回值
DECLARE @OUT int, @RETURN int
EXEC @RETURN =Proc_Test; 1
0,
@OUT output
SELECT [ 返回值 ] = @RETURN, [ OUTPUT值 ] = @OUT
返回值 OUTPUT值
-- --------- -----------
1 0
-- ---------------------------------------------------
-- SP_EXECUTESQL中的OUTPUT参数获取
-- ---------------------------------------------------
DECLARE @Para1 int, @Para2 int, @SUM int
EXECUTE SP_EXECUTESQL
N ' SELECT @SUM=@Para1+@Para2 ',
N ' @Para1 INT,@Para2 INT,@SUM INT OUTPUT ',
5, 5, @SUM OUTPUT
SELECT [ OUTPUT值 ] = @SUM
OUTPUT值
-- ---------
10
======================================================================
下面在.net下调用存储过程:
<%@ Page Language=
"
C#
" AutoEventWireup=
"
true
" CodeFile=
"
Default.aspx.cs
" Inherits=
"
_Default
" %>
<!DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
<html xmlns= " http://www.w3.org/1999/xhtml ">
<head runat= " server ">
<title>使用存储过程</title>
<mce:style type= " text/css "><!--
#form1
{
margin-left: 206px;
}
-->
</mce:style><style type= " text/css " mce_bogus= " 1 ">
#form1
{
margin-left: 206px;
}
</style>
</head>
<body>
<form id= " form1 " runat= " server " style= " border-style: none; width: 339px; ">
<div>
</div>
<asp:Label ID= " Label3 " runat= " server " Text= " 输 入 参 数: "></asp:Label>
<asp:TextBox ID= " Input " runat= " server " BorderStyle= " NotSet "></asp:TextBox>
<asp:ImageButton ID= " ImageButton1 " runat= " server " ImageUrl= " ~/提交.GIF "
οnclick= " ImageButton1_Click " style= " height: 20px " />
<hr width= " 95% " />
<br />
<asp:Label ID= " Label1 " runat= " server " Text= " OUTPUT参数: "></asp:Label>
<asp:Label ID= " Output " runat= " server " BorderColor= " #6600FF " BorderStyle= " None "
BorderWidth= " 1px " Width= " 100px ">暂无</asp:Label>
<hr width= " 95% " />
<br />
<asp:Label ID= " Label4 " runat= " server " Text= " RETURN返回: "></asp:Label>
<asp:Label ID= " Return " runat= " server " BorderColor= " #6600FF " BorderWidth= " 1px "
Width= " 100px " BorderStyle= " None ">暂无</asp:Label>
</form>
</body>
</html>
<%@ Page Language= " C# " AutoEventWireup= " true " CodeFile= " Default.aspx.cs " Inherits= " _Default " %>
<!DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
<html xmlns= " http://www.w3.org/1999/xhtml ">
<head runat= " server ">
<title>使用存储过程</title>
<mce:style type= " text/css "><!--
#form1
{
margin-left: 206px;
}
-->
</mce:style>
<style type= " text/css " mce_bogus= " 1 ">
#form1
{
margin-left: 206px;
}
</style>
</head>
<body>
<form id= " form1 " runat= " server " style= " border-style: none; width: 339px; ">
<div>
</div>
<asp:Label ID= " Label3 " runat= " server " Text= " 输 入 参 数: "></asp:Label>
<asp:TextBox ID= " Input " runat= " server " BorderStyle= " NotSet "></asp:TextBox>
<asp:ImageButton ID= " ImageButton1 " runat= " server " ImageUrl= " ~/提交.GIF "
οnclick= " ImageButton1_Click " style= " height: 20px " />
<hr width= " 95% " />
<br />
<asp:Label ID= " Label1 " runat= " server " Text= " OUTPUT参数: "></asp:Label>
<asp:Label ID= " Output " runat= " server " BorderColor= " #6600FF " BorderStyle= " None "
BorderWidth= " 1px " Width= " 100px ">暂无</asp:Label>
<hr width= " 95% " />
<br />
<asp:Label ID= " Label4 " runat= " server " Text= " RETURN返回: "></asp:Label>
<asp:Label ID= " Return " runat= " server " BorderColor= " #6600FF " BorderWidth= " 1px "
Width= " 100px " BorderStyle= " None ">暂无</asp:Label>
</form>
</body>
</html>
protected void ImageButton1_Click( object sender, ImageClickEventArgs e)
{
// 定义数据库连接和SqlCommand对象
SqlConnection Conn= new SqlConnection(ConfigurationManager.ConnectionStrings[ " TestConnection "].ToString());
SqlCommand Cmd= new SqlCommand( " Proc_Test;1 ",Conn);
Cmd.CommandType = CommandType.StoredProcedure;
// 指定参数类型
SqlParameter input = Cmd.Parameters.Add( " @INPUT ", SqlDbType.Int);
SqlParameter output = Cmd.Parameters.Add( " @OUTPUT ", SqlDbType.Int);
SqlParameter return_ = Cmd.Parameters.Add( " @RETURN ", SqlDbType.Int);
// 指定参数方向
input.Direction = ParameterDirection.Input;
output.Direction = ParameterDirection.Output;
return_.Direction = ParameterDirection.ReturnValue;
// 参数赋值
if (Input.Text == "")
{
input.Value = 0;
}
else
{
input.Value = Convert.ToInt32(Input.Text);
}
// 调用存储过程
Conn.Open();
Cmd.ExecuteNonQuery();
Conn.Close();
Output.Text = output.Value.ToString(); // 获取output值
Return.Text = return_.Value.ToString(); // 获取返回值
}
<!DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
<html xmlns= " http://www.w3.org/1999/xhtml ">
<head runat= " server ">
<title>使用存储过程</title>
<mce:style type= " text/css "><!--
#form1
{
margin-left: 206px;
}
-->
</mce:style><style type= " text/css " mce_bogus= " 1 ">
#form1
{
margin-left: 206px;
}
</style>
</head>
<body>
<form id= " form1 " runat= " server " style= " border-style: none; width: 339px; ">
<div>
</div>
<asp:Label ID= " Label3 " runat= " server " Text= " 输 入 参 数: "></asp:Label>
<asp:TextBox ID= " Input " runat= " server " BorderStyle= " NotSet "></asp:TextBox>
<asp:ImageButton ID= " ImageButton1 " runat= " server " ImageUrl= " ~/提交.GIF "
οnclick= " ImageButton1_Click " style= " height: 20px " />
<hr width= " 95% " />
<br />
<asp:Label ID= " Label1 " runat= " server " Text= " OUTPUT参数: "></asp:Label>
<asp:Label ID= " Output " runat= " server " BorderColor= " #6600FF " BorderStyle= " None "
BorderWidth= " 1px " Width= " 100px ">暂无</asp:Label>
<hr width= " 95% " />
<br />
<asp:Label ID= " Label4 " runat= " server " Text= " RETURN返回: "></asp:Label>
<asp:Label ID= " Return " runat= " server " BorderColor= " #6600FF " BorderWidth= " 1px "
Width= " 100px " BorderStyle= " None ">暂无</asp:Label>
</form>
</body>
</html>
<%@ Page Language= " C# " AutoEventWireup= " true " CodeFile= " Default.aspx.cs " Inherits= " _Default " %>
<!DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
<html xmlns= " http://www.w3.org/1999/xhtml ">
<head runat= " server ">
<title>使用存储过程</title>
<mce:style type= " text/css "><!--
#form1
{
margin-left: 206px;
}
-->
</mce:style>
<style type= " text/css " mce_bogus= " 1 ">
#form1
{
margin-left: 206px;
}
</style>
</head>
<body>
<form id= " form1 " runat= " server " style= " border-style: none; width: 339px; ">
<div>
</div>
<asp:Label ID= " Label3 " runat= " server " Text= " 输 入 参 数: "></asp:Label>
<asp:TextBox ID= " Input " runat= " server " BorderStyle= " NotSet "></asp:TextBox>
<asp:ImageButton ID= " ImageButton1 " runat= " server " ImageUrl= " ~/提交.GIF "
οnclick= " ImageButton1_Click " style= " height: 20px " />
<hr width= " 95% " />
<br />
<asp:Label ID= " Label1 " runat= " server " Text= " OUTPUT参数: "></asp:Label>
<asp:Label ID= " Output " runat= " server " BorderColor= " #6600FF " BorderStyle= " None "
BorderWidth= " 1px " Width= " 100px ">暂无</asp:Label>
<hr width= " 95% " />
<br />
<asp:Label ID= " Label4 " runat= " server " Text= " RETURN返回: "></asp:Label>
<asp:Label ID= " Return " runat= " server " BorderColor= " #6600FF " BorderWidth= " 1px "
Width= " 100px " BorderStyle= " None ">暂无</asp:Label>
</form>
</body>
</html>
protected void ImageButton1_Click( object sender, ImageClickEventArgs e)
{
// 定义数据库连接和SqlCommand对象
SqlConnection Conn= new SqlConnection(ConfigurationManager.ConnectionStrings[ " TestConnection "].ToString());
SqlCommand Cmd= new SqlCommand( " Proc_Test;1 ",Conn);
Cmd.CommandType = CommandType.StoredProcedure;
// 指定参数类型
SqlParameter input = Cmd.Parameters.Add( " @INPUT ", SqlDbType.Int);
SqlParameter output = Cmd.Parameters.Add( " @OUTPUT ", SqlDbType.Int);
SqlParameter return_ = Cmd.Parameters.Add( " @RETURN ", SqlDbType.Int);
// 指定参数方向
input.Direction = ParameterDirection.Input;
output.Direction = ParameterDirection.Output;
return_.Direction = ParameterDirection.ReturnValue;
// 参数赋值
if (Input.Text == "")
{
input.Value = 0;
}
else
{
input.Value = Convert.ToInt32(Input.Text);
}
// 调用存储过程
Conn.Open();
Cmd.ExecuteNonQuery();
Conn.Close();
Output.Text = output.Value.ToString(); // 获取output值
Return.Text = return_.Value.ToString(); // 获取返回值
}