如何在c#中获得SQL Server存储过程返回值?

本文关键字：Server SQL 存储过程返回值 | 更新日期: 2023-09-27 18:17:19

我是c#和SQL Server的初学者，我编写了这个用于在SQL Server中创建存储过程的查询:

create procedure newBehzad 
    @id bigint
as
    DECLARE @ResultValue int
    select *
    from TABLEA
    where id > @id
    SET  @ResultValue = -5
go

一切正常，我写了这段c#代码来调用这个存储过程，它返回一个值:

using (var conn = new SqlConnection(connectionString))
using (var command = new SqlCommand("newBehzad", conn)
{
    CommandType = CommandType.StoredProcedure
})
{
    conn.Open();
    command.Parameters.Add("@id", SqlDbType.BigInt).Value = 2;
    command.Parameters.Add("@ResultValue", SqlDbType.Int);
    SqlParameter retval = command.Parameters.Add("@ResultValue", SqlDbType.Int);
    retval.Direction = ParameterDirection.ReturnValue;
    retunvalue = (string)command.Parameters["@ResultValue"].Value;
    //SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
    command.ExecuteNonQuery();
    conn.Close();
}
MessageBox.Show(returnValue);

但是当我运行c# windows应用程序时，我得到这个错误:

过程或函数newBehzad指定的参数太多。

我怎么解决这个问题?谢谢。

如何在c#中获得SQL Server存储过程返回值?

将程序更改为:

create procedure newBehzad @id bigint, @ResultValue int OUT
as
SET  @ResultValue = 0
BEGIN
    select *from TABLEA
    where id>@id
    SET  @ResultValue = -5
END
go

请试着这样想:

object returnValue = null;
            using (var conn = new System.Data.SqlClient.SqlConnection(AbaseDB.DBFactory.GetInstance().GetConnectionString()))
            {
                using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("newBehzad", conn) { CommandType = CommandType.StoredProcedure })
                {
                    conn.Open();
                    command.Parameters.Add("@id", SqlDbType.BigInt).Value = 2;
                    command.Parameters.Add("@ResultValue", SqlDbType.Int).Direction  = ParameterDirection.Output;
                    command.ExecuteNonQuery();
                    returnValue = command.Parameters["@ResultValue"].Value;
                    conn.Close();
                }
                if (returnValue != null)
                    MessageBox.Show(returnValue.ToString());
            }

using (var conn = new SqlConnection(connectionString))
            using (var command = new SqlCommand("newBehzad", conn)
            {
                CommandType = CommandType.StoredProcedure
            })
            {
                conn.Open();
                command.Parameters.Add("@id", SqlDbType.BigInt).Value = 2;
               // command.Parameters.Add("@ResultValue", SqlDbType.Int); Comment this line

                SqlParameter retval = command.Parameters.Add("@ResultValue", SqlDbType.Int);
                retval.Direction = ParameterDirection.ReturnValue;
                retunvalue = (string)command.Parameters["@ResultValue"].Value;
                //SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
                command.ExecuteNonQuery();
                conn.Close();
            }
            MessageBox.Show(returnValue);

首先，您需要更改存储过程以返回值:

create procedure newBehzad @id bigint
as
    DECLARE @ResultValue int
    select *from TABLEA
    where id>@id
    SET  @ResultValue = -5
    Return @ResultValue
go

然后用:

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();        
    using (var cmd = new SqlCommand("newBehzad", conn)
    {
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter retval = new SqlParameter();
        retval.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add("@id", SqlDbType.BigInt).Value = 2;  
        cmd.Parameters.Add(retval);
        cmd.ExecuteNonQuery();
        returnValue = (int)retval.Value;
    }
}

相关文章：

最新信息: