I am using SQL Server. I am calling a stored procedure from another stored procedure.
我正在使用SQL Server。我从另一个存储过程调用存储过程。
I want to return multiple values from the first stored procedure.
我想从第一个存储过程返回多个值。
Ex: I am calling Sub_SP
from Master_SP
. Sub_SP
will return multiple values to Master_SP
.
例如:我从Master_SP调用Sub_SP。 Sub_SP将向Master_SP返回多个值。
Can anyone give an example with OUTPUT parameters?
任何人都可以举例说明OUTPUT参数吗?
Thank you.
谢谢。
3 个解决方案
#1
16
ALTER procedure ashwin @empid int,@empname varchar(20) output,@age int output
as
select @empname=ename,@age=age
from emp where empid=@empid;
declare @ename varchar(20),@age int
execute ashwin 101,@ename out,@age out
select @ename,@age;
//------------
// ------------
namespace sqlserver
{
class Program
{
static void Main(string[] args)
{
createconnection();
}
public static void createconnection()
{
SqlConnection con=new SqlConnection("Data Source=ASHWIN\\SQLEXPRESS;Initial Catalog=employee;Integrated Security=True;Pooling=False");
con.Open();
SqlCommand cmd=new SqlCommand("ashwin",con);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@empid",SqlDbType.Int,10,"empid"));
cmd.Parameters.Add(new SqlParameter("@empname", SqlDbType.VarChar, 20,ParameterDirection.Output,false,0,20,"ename",DataRowVersion.Default,null));
cmd.Parameters.Add(new SqlParameter("@age", SqlDbType.Int, 20, ParameterDirection.Output, false, 0, 10, "age", DataRowVersion.Default, null));
cmd.Parameters[0].Value = 101;
cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
cmd.ExecuteNonQuery();
string name = (string)cmd.Parameters["@empname"].Value;
int age = Convert.ToInt32(cmd.Parameters["@age"].Value);
Console.WriteLine("the name is {0}--and age is {1}", name,age);
Console.ReadLine();
}
}
#2
7
How about this:
这个怎么样:
CREATE PROCEDURE dbo.SubSP
@Value1 INT OUTPUT, @Value2 INT OUTPUT
AS
-- just return two values into the OUTPUT parameters somehow....
SELECT @Value1 = 42, @Value2 = 4711
Test the dbo.SubSP
:
测试dbo.SubSP:
DECLARE @Out1 INT, @Out2 INT
EXEC dbo.SubSP @Out1 OUTPUT, @Out2 OUTPUT -- int
SELECT @Out1, @Out2
Gives output:
给出输出:
@Out1 @Out2
42 4711
Then create "master" stored procedure:
然后创建“主”存储过程:
CREATE PROCEDURE dbo.MasterSP
@SomeValue1 INT OUTPUT, @SomeValue2 INT OUTPUT, @SomeValue3 INT OUTPUT
AS BEGIN
DECLARE @Out1 INT, @Out2 INT
-- call the "sub" stored procedure and capture OUTPUT values
EXEC dbo.SubSP @Out1 OUTPUT, @Out2 OUTPUT
-- return those values - plus some others - from master stored procedure
SELECT @SomeVAlue1 = @Out1, @SomeVAlue2 = @Out2, @SomeValue3 = 9901
END
Test master stored proc:
测试主存储过程:
DECLARE @Some1 INT, @Some2 INT, @Some3 INT
EXECUTE dbo.MasterSP @Some1 OUTPUT, @Some2 OUTPUT, @Some3 OUTPUT
SELECT @Some1, @Some2, @Some3
Gives output:
给出输出:
(No column name) (No column name) (No column name)
42 4711 9901
Does this work? Does this solve your problem? If not: where are you stuck, what exactly is the problem?
这有用吗?这会解决您的问题吗?如果不是:你被困在哪里,究竟是什么问题?
#3
1
I had the same problem and I am working in VB. I have tried the @Scorpian275 's answer. This is the same solution as @Scorpian275 provided but it is for the VB. The sql part is the same.
我有同样的问题,我在VB工作。我试过@ Scorpian275的答案。这与@ Scorpian275提供的解决方案相同,但它适用于VB。 sql部分是一样的。
Public Shared Sub createConnection()
Dim con As SqlConnection = New SqlConnection("Data Source=ASHWIN\\SQLEXPRESS;Initial Catalog=employee;Integrated Security=True;Pooling=False")
con.Open
Dim cmd As SqlCommand = New SqlCommand("ashwin", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@empid", SqlDbType.Int, 10, "empid"))
cmd.Parameters.Add(New SqlParameter("@empname", SqlDbType.VarChar, 20, ParameterDirection.Output, false, 0, 20, "ename", DataRowVersion.Default, Nothing))
cmd.Parameters.Add(New SqlParameter("@age", SqlDbType.Int, 20, ParameterDirection.Output, false, 0, 10, "age", DataRowVersion.Default, Nothing))
cmd.Parameters(0).Value = 101
cmd.UpdatedRowSource = UpdateRowSource.OutputParameters
cmd.ExecuteNonQuery
Dim name As String = CType(cmd.Parameters("@empname").Value,String)
Dim age As Integer = Convert.ToInt32(cmd.Parameters("@age").Value)
Console.WriteLine("the name is {0}--and age is {1}", name, age)
Console.ReadLine
End Sub
The last parameter in cmd.Parameters.Add(New SqlParameter("@age", SqlDbType.Int, 20, ParameterDirection.Output, false, 0, 10, "age", DataRowVersion.Default, Nothing)) is the initial value that we provide for those variables (Here Nothing).
cmd.Parameters.Add中的最后一个参数(New SqlParameter(“@ age”,SqlDbType.Int,20,ParameterDirection.Output,false,0,10,“age”,DataRowVersion.Default,Nothing))是初始值我们提供这些变量(这里没什么)。
#1
16
ALTER procedure ashwin @empid int,@empname varchar(20) output,@age int output
as
select @empname=ename,@age=age
from emp where empid=@empid;
declare @ename varchar(20),@age int
execute ashwin 101,@ename out,@age out
select @ename,@age;
//------------
// ------------
namespace sqlserver
{
class Program
{
static void Main(string[] args)
{
createconnection();
}
public static void createconnection()
{
SqlConnection con=new SqlConnection("Data Source=ASHWIN\\SQLEXPRESS;Initial Catalog=employee;Integrated Security=True;Pooling=False");
con.Open();
SqlCommand cmd=new SqlCommand("ashwin",con);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@empid",SqlDbType.Int,10,"empid"));
cmd.Parameters.Add(new SqlParameter("@empname", SqlDbType.VarChar, 20,ParameterDirection.Output,false,0,20,"ename",DataRowVersion.Default,null));
cmd.Parameters.Add(new SqlParameter("@age", SqlDbType.Int, 20, ParameterDirection.Output, false, 0, 10, "age", DataRowVersion.Default, null));
cmd.Parameters[0].Value = 101;
cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
cmd.ExecuteNonQuery();
string name = (string)cmd.Parameters["@empname"].Value;
int age = Convert.ToInt32(cmd.Parameters["@age"].Value);
Console.WriteLine("the name is {0}--and age is {1}", name,age);
Console.ReadLine();
}
}
#2
7
How about this:
这个怎么样:
CREATE PROCEDURE dbo.SubSP
@Value1 INT OUTPUT, @Value2 INT OUTPUT
AS
-- just return two values into the OUTPUT parameters somehow....
SELECT @Value1 = 42, @Value2 = 4711
Test the dbo.SubSP
:
测试dbo.SubSP:
DECLARE @Out1 INT, @Out2 INT
EXEC dbo.SubSP @Out1 OUTPUT, @Out2 OUTPUT -- int
SELECT @Out1, @Out2
Gives output:
给出输出:
@Out1 @Out2
42 4711
Then create "master" stored procedure:
然后创建“主”存储过程:
CREATE PROCEDURE dbo.MasterSP
@SomeValue1 INT OUTPUT, @SomeValue2 INT OUTPUT, @SomeValue3 INT OUTPUT
AS BEGIN
DECLARE @Out1 INT, @Out2 INT
-- call the "sub" stored procedure and capture OUTPUT values
EXEC dbo.SubSP @Out1 OUTPUT, @Out2 OUTPUT
-- return those values - plus some others - from master stored procedure
SELECT @SomeVAlue1 = @Out1, @SomeVAlue2 = @Out2, @SomeValue3 = 9901
END
Test master stored proc:
测试主存储过程:
DECLARE @Some1 INT, @Some2 INT, @Some3 INT
EXECUTE dbo.MasterSP @Some1 OUTPUT, @Some2 OUTPUT, @Some3 OUTPUT
SELECT @Some1, @Some2, @Some3
Gives output:
给出输出:
(No column name) (No column name) (No column name)
42 4711 9901
Does this work? Does this solve your problem? If not: where are you stuck, what exactly is the problem?
这有用吗?这会解决您的问题吗?如果不是:你被困在哪里,究竟是什么问题?
#3
1
I had the same problem and I am working in VB. I have tried the @Scorpian275 's answer. This is the same solution as @Scorpian275 provided but it is for the VB. The sql part is the same.
我有同样的问题,我在VB工作。我试过@ Scorpian275的答案。这与@ Scorpian275提供的解决方案相同,但它适用于VB。 sql部分是一样的。
Public Shared Sub createConnection()
Dim con As SqlConnection = New SqlConnection("Data Source=ASHWIN\\SQLEXPRESS;Initial Catalog=employee;Integrated Security=True;Pooling=False")
con.Open
Dim cmd As SqlCommand = New SqlCommand("ashwin", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@empid", SqlDbType.Int, 10, "empid"))
cmd.Parameters.Add(New SqlParameter("@empname", SqlDbType.VarChar, 20, ParameterDirection.Output, false, 0, 20, "ename", DataRowVersion.Default, Nothing))
cmd.Parameters.Add(New SqlParameter("@age", SqlDbType.Int, 20, ParameterDirection.Output, false, 0, 10, "age", DataRowVersion.Default, Nothing))
cmd.Parameters(0).Value = 101
cmd.UpdatedRowSource = UpdateRowSource.OutputParameters
cmd.ExecuteNonQuery
Dim name As String = CType(cmd.Parameters("@empname").Value,String)
Dim age As Integer = Convert.ToInt32(cmd.Parameters("@age").Value)
Console.WriteLine("the name is {0}--and age is {1}", name, age)
Console.ReadLine
End Sub
The last parameter in cmd.Parameters.Add(New SqlParameter("@age", SqlDbType.Int, 20, ParameterDirection.Output, false, 0, 10, "age", DataRowVersion.Default, Nothing)) is the initial value that we provide for those variables (Here Nothing).
cmd.Parameters.Add中的最后一个参数(New SqlParameter(“@ age”,SqlDbType.Int,20,ParameterDirection.Output,false,0,10,“age”,DataRowVersion.Default,Nothing))是初始值我们提供这些变量(这里没什么)。