how do return and use From Stored Procedure Numeric Filed Value

See more:

SQL-Server-2005

C#

SQL

SQL-Server

, +

hi all
i want return one numeric field in sql server and use from it in c#

i wrote this code but not correct

please help me

Stored Procedure Code

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[Calc_Product_Price]
(@product_id int,
 @Price int output)
as

if not exists (select * from Products where [product_id]=@product_id)
return -1
else
begin
select @Price=[product_sell_price] from Products where [product_id]=@product_id

print @Price
return @Price
end

C# Code :

public int  Calc_Product_price(int Product_id)
{
    SqlConnection MySqlConnection = new SqlConnection(ConnectionMain.strconnection);
    SqlCommand MySqlCommand = MySqlConnection.CreateCommand();
    MySqlCommand.CommandText = "Calc_Product_Price";
    MySqlCommand.CommandType = CommandType.StoredProcedure;

    MySqlCommand.Parameters.Add("@product_id", SqlDbType.BigInt, 8).Value = Product_id;
    MySqlCommand.Parameters.Add("@Price", SqlDbType.BigInt, 8).Value = 0;
    SqlParameter retval = MySqlCommand.Parameters.Add("@Price", SqlDbType.BigInt );
    retval.Direction = ParameterDirection.ReturnValue;


    if (MySqlConnection.State != ConnectionState.Open)
    {
        MySqlConnection.Open();
    }

    MySqlCommand.ExecuteNonQuery();
    int result =(int) MySqlCommand.Parameters["@Price"].Value;

    if (MySqlConnection.State != ConnectionState.Closed)
    {
        MySqlConnection.Close();
    }
    if (result == 1)
    {
        return result;
    }
    else
    {
        return -1;
    }
}