Share via

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

  • 05/07/2009

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

Comments