Stored Procedures

Parameterized commands are just a short step from commands that call full-fledged stored
procedures.
A stored procedure, of course, is a batch of one or more SQL statements that are stored in the
database. Stored procedures are similar to functions in that they are well-encapsulated blocks of
logic that can accept data (through input parameter) and return data (through result sets and output
parameters).

Stored procedures have many benefits:
• They are easier to maintain. For example, you can optimize the commands in a stored procedure
without recompiling the application that uses it.
• They allow you to implement more secure database usage. For example, you can allow the
Windows account that runs your ASP.NET code to use certain stored procedures but restrict
access to the underlying tables.
• They can improve performance. Because a stored procedure batches together multiple
statements, you can get a lot of work done with just one trip to the database server. If your
database is on another computer, this reduces the total time to perform a complex task
dramatically.

Here’s the SQL code needed to create a stored procedure for inserting a single record into the
Employees table. This stored procedure isn’t in the Northwind database initially, so you’ll need to
add it to the database (using a tool such as Enterprise Manager or Query Analyzer) before you use it.
CREATE PROCEDURE InsertEmployee
@TitleOfCourtesy varchar(25),
@LastName varchar(20),
@FirstName varchar(10),
@EmployeeID int OUTPUT
AS
INSERT INTO Employees
(TitleOfCourtesy, LastName, FirstName, HireDate)
VALUES (@TitleOfCourtesy, @LastName, @FirstName, GETDATE());
SET @EmployeeID = @@IDENTITY
GO
This stored procedure takes three parameters for the employee’s title of courtesy, last name, and
first name. It returns the ID of the new record through the output parameter called @EmployeeID,
which is retrieved after the INSERT statement using the @@IDENTITY function. This is one example
of a simple task that a stored procedure can make much easier. Without using a stored procedure,
it’s quite awkward to try to determine the automatically generated identity value of a new record
you’ve just inserted.
Next, you can create a SqlCommand to wrap the call to the stored procedure. This command
takes the same three parameters as inputs and uses @@IDENTITY to get and then return the
ID of the new record. Here is the first step, which creates the required objects and sets the
InsertEmployee as the command text:
string connectionString =
WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
// Create the command for the InsertEmployee stored procedure.
SqlCommand cmd = new SqlCommand("InsertEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
Now you need to add the stored procedure’s parameters to the Command.Parameters collection.
When you do, you need to specify the exact data type and length of the parameter so that it
matches the details in the database.

Here’s how it works for a single parameter:

cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
cmd.Parameters["@TitleOfCourtesy"].Value = title;

The first line creates a new SqlParameter object; sets its name, type, and size in the constructor;
and adds it to the Parameters collection. The second line assigns the value for the parameter, which will be sent to the stored procedure when you execute the command.

Now you can add the next two parameters in a similar way:

cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
cmd.Parameters["@LastName"].Value = lastName;
cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
cmd.Parameters["@FirstName"].Value = firstName;
The last parameter is an output parameter, which allows the stored procedure to return information
to your code. Although this Parameter object is created in the same way, you must make
sure you specify it is an output parameter by setting its Direction property to Output. You don’t
need to supply a value.

cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;

Finally, you can open the connection and execute the command with the ExecuteNonQuery()
method. When the command is completed, you can read the output value, as shown here:

con.Open();
try
{
int numAff = cmd.ExecuteNonQuery();
HtmlContent.Text += String.Format(
"Inserted {0} record(s)
", numAff);
// Get the newly generated ID.
empID = (int)cmd.Parameters["@EmployeeID"].Value;
HtmlContent.Text += "New ID: " + empID.ToString();
}
finally
{
con.Close();
}

In the next chapter, you’ll see a small but fully functional database component that does all its
work through stored procedures.