SQL Server Stored Procedures

This tutorial gives an overview of how to use SQL Server Stored Procedures in a web application.

Stored Procedures are compiled SQL code stored in the database. Calling stored procedures as opposed to sending over query strings improves the performance of a web application. Not only is there less network traffic since only short commands are sent instead of long query stings, but the execution of the actually code itself also improves. The reason is because a stored procedure is already compiled ahead of time. Stored procedures are also cached by SQL Server when they are run to speed things up for subsequent calls.

Other than performance, stored procedures are also helpful because they provide another layer of abstraction for your web application. For instance, you can change a query in a stored procedure and get different results without having to recompile your objects. Using stored procedures also makes your objects cleaner, and SQL Server’s convenient backup tool makes it easy to back them up.

To create a stored procedure, we can use the SQL Query Analyzer or the SQL Server Enterprise Manager. I find it easier to use the query analyzer because it allows you to test your query before putting it in a procedure and then tests the procedure after it has been created. However, enterprise manager provides an easy interface for viewing all of the existing stored procedures and editing them.

Suppose we have the following query that retrieves messages from a given thread:

SELECT message_id,
       thread_id,
       user_id,
       first_names,
       last_name,
       email,
       subject,
       body,
       date_submitted,
       category_name,
       category_id,
       last_edited
FROM    message_view
WHERE thread_id = @iThreadID
ORDER BY date_submitted asc

To put this query in a stored procedure using the query analyzer, we simply have to give it a name (GetThreadMessages) and tell it what inputs (@iThreadID int) it requires. The name of the procedure goes in the create statement, then come the comma separated inputs, and finally the AS keyword followed by the procedure. The resulting statement would look like this:


Creating the GetThreadMessages procedure using the query analyzer

Then, to test out the procedure in the query analyzer, we just run it with comma separated inputs (there’s only one input in this case).


Executing GetThreadMessages procedure with thread_id 1

Finally, if we want to view it in the enterprise manager, we just go to the stored procedures section, right click on it, and choose properties. Here we can edit it and save our changes. The other options when you right click it allow you to rename it, delete it, and copy it much like you could with a file. Opening up the properties in enterprise manager looks like this.


GetThreadMessages properties under enterprise manager

Now that we’ve made our stored procedure, we just need to execute it from our object model. Executing it from our object model is almost the same as executing a normal query. The only differences are instead of providing the query, we provide the name of the stored procedure, and we also specify that it is a stored procedure. The c# code to do this would look like this (sorry, have to leave out the connection string):

SqlConnection myConnection = new SqlConnection(strConnection);
SqlCommand myCommand  = new SqlCommand("GetThreadMessages", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

Don’t forget to bind the input variables like you normally would:

SqlParameter ThreadID = new SqlParameter("@iThreadID", SqlDbType.Int, 4);
ThreadID.Value        = iThreadID;
myCommand.Parameters.Add(ThreadID);

Hope you find this somewhat useful.