Stored Procedure

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.


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:


Create Procedure GetThreadMessages
@iThreadID int

AS

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