c＃中 调用带输出参数output的存储过程，如何获得该返回值？

[zhengyingcan]

我的存储过程标题如下:
ALTER proc [dbo].[brpbomweight] @cinvcode varchar(50), @dec3 decimal(18,6) output
as

我的调用C#代码为
DataView dv1 = new DataView(dt1);
string cinvcode = "";
decimal weight = 0;
string conn = command.Class1.cn;
SqlConnection sqlcon = new SqlConnection(conn);
SqlCommand comand = new SqlCommand();
comand.Connection = sqlcon;
SqlParameter s = new SqlParameter("@dec3",SqlDbType.Decimal,9);
s.Direction = ParameterDirection.Output;
comand.Parameters.Add(s);
sqlcon.Open();
foreach (DataRowView drv in dv1)//循环判断主件编码
{
cinvcode = drv[0].ToString();
string brpsql = "declare @dec3 decimal(18,6) exec brpbomweight '" + cinvcode + "', @dec3 output ";
comand.CommandText = brpsql;
comand.ExecuteNonQuery();
weight = (Decimal)comand.Parameters["dec3"].Value;
string comsql = "UPDATE Cbom SET weight="+weight+" WHERE cinvcode='"+cinvcode+"'";
command.Class1.cmdexec(comsql);
}
sqlcon.Close();

系统报错的提示是 变量名 '@dec3' 已声明。变量名在查询批次或存储过程内部必须唯一。
那么高人,我的代码应该如何改呢,谢谢

[feng1456]

你好，你的问题我看了下，这个写法是有点问题的，我给你写了一个例子：

//connection
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = @"Data Source=xxx-pc;User ID=sa;Password=1234;Initial Catalog=test;";

            //commond and parameter
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            cmd.Parameters.Add(new SqlParameter("@in", SqlDbType.Int));
            cmd.Parameters["@in"].Value = 10;

            cmd.Parameters.Add(new SqlParameter("@out", SqlDbType.Int));
            cmd.Parameters["@out"].Value = 10;

            cmd.Parameters["@out"].Direction = ParameterDirection.Output;
            cmd.CommandText = "[proc_test]";
            conn.Open();

            cmd.ExecuteNonQuery();

            int result = (int)cmd.Parameters["@out"].Value;

            Console.WriteLine("the return value of proc_test is :" + result.ToString()+".");

需要注意的是：output参数传进去的时候也是要赋值的，类似与c#函数的ref参数；之所以报那个错误是因为你的commond sql里面写的不对，不应该再声明变量，请参见我的例子.如有问题请发送邮件至：[e-mail]

[feng1456]

另外，存储过程很简单,代码如下：

alter procedure [dbo].[proc_test]
@in int,
@out int output
AS
select @out = 2*@in;
return @out
GO

调用测试如下

declare @out int;
set @out = 10;
exec [proc_test] 10,@out;

[zhengyingcan]

改成如下,没有报错,但是出来的结果都是0 不知道是什么原因 DataView dv1 = new DataView(dt1); string cinvcode = ""; decimal weight = 0; string conn = command.Class1.cn; SqlConnection sqlcon = new SqlConnection(conn); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = sqlcon; SqlParameter s = new SqlParameter("@dec3",SqlDbType.Decimal,13); s.Direction = ParameterDirection.Output; cmd.Parameters.Add(s); cmd.Parameters.Add(new SqlParameter("@cinvcode", SqlDbType.NVarChar, 50)); cmd.CommandText = "brpbomweight"; sqlcon.Open(); foreach (DataRowView drv in dv1)//循环判断主件编码 { cinvcode = drv[0].ToString(); //string brpsql = "declare @dec3 decimal(18,6) exec brpbomweight '" + cinvcode + "', @dec3 output "; //comand.CommandText = brpsql; cmd.Parameters["@dec3"].Value = 0; cmd.Parameters["@cinvcode"].Value = cinvcode; cmd.ExecuteNonQuery(); weight = (Decimal)cmd.Parameters["@dec3"].Value; string comsql = "UPDATE Cbom SET weight="+weight+" WHERE cinvcode='"+cinvcode+"'"; command.Class1.cmdexec(comsql); } sqlcon.Close(); MessageBox.Show("成功执行"); }

[zhengyingcan]

我用以下代码返回 也是0,不知道是什么原因 string strConn = command.Class1.cn; SqlConnection conn = new SqlConnection(strConn); conn.Open(); SqlCommand cmd = new SqlCommand("brpbomweight", conn); cmd.CommandType = CommandType.StoredProcedure; //输入参数 SqlParameter param1 = new SqlParameter("@cinvcode", SqlDbType.VarChar, 50); param1.Value = "1201SPE13601"; cmd.Parameters.Add(param1); //输出参数 SqlParameter param = new SqlParameter("@dec3", SqlDbType.Decimal); param.Direction = ParameterDirection.Output; cmd.Parameters.Add(param); //执行存储过程 cmd.ExecuteNonQuery(); conn.Close(); //输出值 MessageBox.Show(param.Value.ToString());