Yesterday, I created a simple stored procedure and used it inside an ASP.NET project. In that example, we used standard Input parameters to insert a record into our users table.

In today's example, we are going to use the same users table, but add two more touches to it. First, we are going to use an Output parameter to return the value of the user's unique ID number. Second, we are going to do the same thing, except with a return value.

Output Parameters

Lets begin with an output parameter example. In this first example, I want to add a user into my table and then get the unique id back. In order to do this, we'll use an output parameter.

This should look very similiar to yesterday's example. The only thing I've added is the extra output parameter and the code used to select the unique id. The output parameter is declared as @user_id with a data type of int and the flag “output” added to it. This lets MSSQL know that it shouldn't be expecting any initial value for it.

In order to actually get the unique id, we use

This line takes our output parameter, @user_id, and sets it equal to the latest unique id in the users table. SCOPE_IDENTITY() is a function that returns the latest unique id created for any table used in this session.

Now that we have created our stored procedure, we can jump into the C# code. Again, the code for this part will look very similiar to the C# code in yesterday's example, with a few minor adjustments.

First, we need to add our new output parameter, @user_id.

SqlParameter param4 = new SqlParameter();
param4.ParameterName = "@user_id";
param4.SqlDbType = SqlDbType.Int;
param4.Direction = ParameterDirection.Output;

In the code above, I created a new SqlParameter with a name of @user_id and the ParameterDirection is set as output. By default, all parameters have a direction of input, so you will need to change that if you're using Output parameters. Once you have created the new parameter, add the parameter to the parameters collection.

cmd.Parameters.Add(param4);

Finally, we need to get our output parameter value once our procedure has been executed. To do this, you will need to do the following.

conn.Open();
cmd.ExecuteNonQuery();
ltlUserId.Text = cmd.Parameters["@user_id"].Value.ToString();
conn.Close();

After I executed my stored procedure, I got my output parameter value by using the Value property. That's all there is to it.

Return Values

Using return values is almost exactly similiar to output parameters, but with a few exceptions.

- Return values are integers, only.
- You do not need to declare a parameter in the stored procedure code
- You do need to create a parameter in your C#/VB.net code, but the parameter name is “RETURN_VALUE” and the ParameterDirection is “ParameterDirection.ReturnValue“.
- To get the return value, you would type in: cmd.Parameters[”RETURN_VALUE”].Value;

Instead of going over every detail, I'll put the stored procedure code below, as well as the C# code.

C# Code
--------------------------------------------------------------------

SqlConnection conn = new SqlConnection("conn_string_here;");
SqlCommand cmd = new SqlCommand("AddUserReturn",conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@username",SqlDbType.VarChar);
param.Value = "sbauer";
SqlParameter param2 = new SqlParameter("@first_name",SqlDbType.VarChar);
param2.Value = "Shane";
SqlParameter param3 = new SqlParameter("@last_name",SqlDbType.VarChar);
param3.Value = "Bauer";
SqlParameter param4= new SqlParameter("RETURN_VALUE",SqlDbType.Int);
param4.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(param);
cmd.Parameters.Add(param2);
cmd.Parameters.Add(param3);
cmd.Parameters.Add(param4);
conn.Open();
cmd.ExecuteNonQuery();
ltlUserId.Text = cmd.Parameters["RETURN_VALUE"].Value.ToString();
conn.Close();

I hope you found my quick overview useful.