SQL 201 — CREATE PROCEDURE

Now that you have the basics of SQL, I think it’s time to learn something a bit more involved.  If you need to accomplish a task within SQL you will run a series of T-SQL statements.  If you want to run those same steps over and over again, you could save them to a file, and reopen that file each time you want to run those steps.  But what if you need someone else to run those steps, and for some reason you can’t send them that file.

There is a way to store a set of steps on the server in such a way anyone with the proper rights could run those commands.  That collection of T-SQL would be called a STORED PROCEDURE.  To CREATE that PROCEDURE, use the following code.

CREATE PROCEDURE procedureName
AS
   <sql command(s)>

Simple, right?  That’s all it would take to save any series of sql commands you’ve written as a named PROCEDURE. To run this procedure after creating it, you simply call:

EXEC procedureName

You could go one step farther.  What if in your sql steps you had to change a value each time you ran the command?

Procedures can accept parameters!  Let’s say you wanted a procedure that would return a list of all the people who logged into your website today.  Let’s also suppose you had a table that created a record with username and a login timestamp each time someone logged in.  Let’s call that table loginHistory.

You could then create a stored procedure to return a list of usernames that logged in on a certain day.

CREATE PROCEDURE getUsersOnDay @lookupDay datetime
AS
SELECT
 userName
FROM loginHistory
WHERE
 loginTimestamp = CONVERT(varchar(10), @lookupDay, 101)

You could then call this procedure and type in any valid date.

EXEC getUsersOnDay GETDATE()

Passing GETDATE() to this command would return the users for today.  You could also use ‘1/1/2006’, etc.  Any date will work.  Please note badly formed dates will break this procedure.  When you develop procedures you’ll have to handle error cases.  I leave that topic to a much later lesson, since it’s such an involved thing.

That’s it for creating procedures in SQL.  If you have any questions, please send them in!  I’m here to help you learn more about SQL.