In SQL, a stored procedure is a type of action.  In general, it is a specially formatted sequence of functions or actions to perform.  It has extensive benefits over writing simple queries in that it is stored in the SQL engine for re-use.

For example, let’s say that you’ve created a table to hold all of your users logins and passwords (and some other info):

CREATE TABLE users (
   userID INT NOT NULL IDENTITY(1,1),
   login NVARCHAR(32) NOT NULL,
   passw NVARCHAR(100) NOT NULL,
   email NVARCHAR(50) NOT NULL,
   uName NVARCHAR(50) NULL,
   uCreated SMALLDATETIME NOT NULL DEFAULT (getdate())
)

On the one hand you can simply write a simple query in your server-side script (here in ASP.NET):

string mySqlQuery = "SELECT * FROM users
   WHERE login = '" + strLogin + "' AND passw = '" + strPass + "'";

And that would work great.  But now lets say you’ve constructed a second table containing more normalized information about the user:

CREATE TABLE usersMetadata (
   userID INT NOT NULL DEFAULT (0),
   dataDescription NVARCHAR(50) NOT NULL,
   theInformation NVARCHAR(1000) NULL
)

So what if you wanted to pull certain information about a specific user?  Once again we can write a simple query:

string mySqlQuery = "SELECT usersMetadata.*,users.*
   FROM usersMetadata
   LEFT JOIN users ON users.userID = usersMetadata.userID
   WHERE userID = " + intUserId + " AND dataDescription = '" + strSomeInfo + "'";

And that would work great too.  But as those of us who develop large-scale web sites know, we re-use queries over and over, frequently.  So what happens when we change the query on one page and neglect to change it on another?  Now we have two pages with different results.  Not good.

Using a stored procedure, we now give ourselves direct access to a centralized query.  This means that if we change it once (on the server), it changes for all instances of it.  So how do we do this?  Writing a stored procedure is relatively simple. 

CREATE PROCEDURE dbo.getUserMetadata
   @userID INT,
   @dataType NVARCHAR(50)
SET NO COUNT ON
BEGIN
   SELECT usersMetadata.*,users.* FROM usersMetadata
   LEFT JOIN users ON users.userID = usersMetadata.userID
   WHERE userID = @userID AND dataDescription = @dataType
END

Calling stored procedures is a snap too. Instead of writing out the entire query, we can just execute the procedure as:

string mySqlQuery = "EXEC getUserMetadata @UserID, @dataType";

Note that we would declare the @ parameters as well

Stored procedures can also be great when you need to extrapolate data from multiple places and perform additional actions on the data before output.  Stored procedures, for example, allow you to create, use and destroy temporary tables of information without affecting the whole.  Say, for example you want to test how your data might react if performed but you don’t want it to go against the live table first.

CREATE PROCEDURE dbo.testUserUpdate
SET NO COUNT ON
BEGIN
   -- this is the temporary table
   CREATE TABLE #users (
      userID INT NOT NULL IDENTITY(1,1),
      login NVARCHAR(32) NOT NULL,
      passw NVARCHAR(32) NOT NULL,
      email NVARCHAR(50) NOT NULL,
      uName NVARCHAR(50) NULL,
      uCreated SMALLDATETIME NOT NULL DEFAULT (getdate())
   )
   -- inserts all users created in the last month to the temporary table
   INSERT INTO #users SELECT users.userID,login,passw,email,uName,uCreated FROM users
   WHERE uCreated > DATEADD('dd',-30,getdate())
-- apply some silly change like altering the password for any user with a login beginning with an a
UPDATE #users SET passw = 'KILLED-YOUR-PASSWORD' WHERE login LIKE 'a%'
   -- now display the data
SELECT * FROM #users ORDER BY #users.login
-- and be sure to destroy the temporary (it is temporary after all)
DROP TABLE #users
SET NOCOUNT OFF
END

This procedure will have the same basic effect as having made the update to the users table but instead applied it to transitory data that was destroyed before leaving the query. Now assuming that the results were good, you could ideally just have a second procedure to actually alter the original table.

Shortly we’ll add to this post with an update. In the meantime, code on!