CODEWITHSUNDEEP
c#sql-serverstored-procedures
user3671271
user3671271

Reputation: 568

How can I get SQL Server stored procedure return value in c#?

I'm beginner in C# and SQL Server, and I wrote this query for creating a stored procedure in SQL Server:

create procedure newBehzad 
    @id bigint
as
    DECLARE @ResultValue int

    select *
    from TABLEA
    where id > @id

    SET  @ResultValue = -5
go

Everything is working, and I wrote this C# code to call that stored procedure and it return a single value:

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

But when I run the C# windows application, I get this error:

Procedure or function newBehzad has too many arguments specified.

How can I solve that? Thanks.

Upvotes: 1

Views: 599

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

First of all you need to change the stored proc to return the value:

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

    Return @ResultValue
go

Then grab it with:

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

But I really can not get why are you selecting data in the stored proc...

Upvotes: 1

Arkadiusz
Arkadiusz

Reputation: 489

Change you procedure to:

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

Please try somethink like this:

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

Upvotes: 1

Aliasghar Bahrami
Aliasghar Bahrami

Reputation: 267

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

Upvotes: 1

Related Questions