从存储过程返回多个值 [英] Returning multiple values from a stored procedure

问题描述

我正在使用 SQL Server.我正在从另一个存储过程调用一个存储过程.

I am using SQL Server. I am calling a stored procedure from another stored procedure.

我想从第一个存储过程返回多个值.

I want to return multiple values from the first stored procedure.

例如:我从 Master_SP 调用 Sub_SP.Sub_SP 将返回多个值给 Master_SP.

Ex: I am calling Sub_SP from Master_SP. Sub_SP will return multiple values to Master_SP.

谁能举一个带有 OUTPUT 参数的例子?

Can anyone give an example with OUTPUT parameters?

谢谢.

推荐答案

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();
        }
    }

这篇关于从存储过程返回多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！