A simple example on how to get Return and Out parameter values using Ado.Net


Another short one.


 


How to capture OUT and RETURN parameter values from a stored procedure in .Net.


 


Well, I’ll let the example speak for itself.


 


First create a stored procedure, this just take an in parameter and an out parameter, it declares a local variable that will be used as the return value.


 


create procedure ParamExample (@inParam int, @outParam int out)


as


begin


               declare @retParam int


               set @outParam = @inParam * 2


               set @retParam = @inParam * 4


               return @retParam


end


 


and test it:


 


declare @in int


declare @out int


declare @ret int


 


set @in = 100


set @out = 0


 


exec @ret = ParamExample @in, @out out


select @out as o, @ret as r


 


This should simply returns 200 for out parameter and 400 for the return.


 


Now, create a console application (C# in my case) replace the Main method with this.


I’ve used two approaches, one verbose and a shorter one, they both to the same thing however.


 


            string cs = @”Data Source=.\sqlexpress;Initial Catalog=Repros;Integrated Security=SSPI”;


            string sql = “ParamExample”;


            try


            {


                // Verbose approach


                using (SqlConnection con = new SqlConnection(cs))


                {


                    con.Open();


 


                    // Create the command, setting the command text to be the stored procedure name


                    SqlCommand cmd = new SqlCommand();


                    cmd.Connection = con;


                    cmd.CommandText = sql;


                    // Also need to specify that this is a stored procedure command (default is Text)


                    cmd.CommandType = System.Data.CommandType.StoredProcedure;


 


                    // Create the input paramenter, set the properites and add to command.


                    SqlParameter inParam = new SqlParameter();


                    inParam.SqlDbType = System.Data.SqlDbType.Int;


                    inParam.ParameterName = “@inParam”;


                    inParam.Direction = System.Data.ParameterDirection.Input;


                    inParam.Value = 100;


                    cmd.Parameters.Add(inParam);


 


                    // Create the out paramenter, set the properites and add to command).


                    SqlParameter outParam = new SqlParameter();


                    outParam.SqlDbType = System.Data.SqlDbType.Int;


                    outParam.ParameterName = “@outParam”;


                    outParam.Value = 100;


                    outParam.Direction = System.Data.ParameterDirection.Output;


                    cmd.Parameters.Add(outParam);


 


                    // Create the return paramenter, set the properites and add to command).


                    SqlParameter retParam = new SqlParameter();


                    retParam.SqlDbType = System.Data.SqlDbType.Int;


                    retParam.ParameterName = “@retParam”;


                    retParam.Direction = System.Data.ParameterDirection.ReturnValue;


                    cmd.Parameters.Add(retParam);


 


                    // Execute the command


                    cmd.ExecuteNonQuery();


 


                    // Get the values


                    int retval = (int)cmd.Parameters[“@retParam”].Value;


                    int outval = (int)cmd.Parameters[“@outParam”].Value;


                    Console.WriteLine(“Return value: {0}, Out value: {1}”, retval, outval);


                    // or get them directly from the parameter variable itself.


                    Console.WriteLine(“Return value: {0}, Out value: {1}”, retParam.Value, outParam.Value);


                    con.Close();


                }


 


                // Short approach


                using (SqlConnection con = new SqlConnection(cs))


                {


                    con.Open();


                    // Create the command, setting the command text to be the stored procedure name


                    SqlCommand cmd = new SqlCommand(sql, con);


                    cmd.CommandType = System.Data.CommandType.StoredProcedure;


                    // Create the input/output/return paramenter


                    cmd.Parameters.AddWithValue(“@inParam”, 100).Direction = System.Data.ParameterDirection.Input;


                    cmd.Parameters.Add(“@outParam”, System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.Output;


                    cmd.Parameters.Add(“@retParam”, System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.ReturnValue;


                    // Execute the command


                    cmd.ExecuteNonQuery();


 


                    // Get the values


                    int retval = (int)cmd.Parameters[“@retParam”].Value;


                    int outval = (int)cmd.Parameters[“@outParam”].Value;


 


                    Console.WriteLine(“Return value: {0}, Out value: {1}”, retval, outval);


                    con.Close();


                }


            }


            catch (SqlException se)


            {


                Console.WriteLine(se);


            }


 


Simple as that J