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.

   <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
FROM loginHistory
 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.

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.


  1. Hey this is awesome I am taking classes but your site has really made it for me more more more 🙂

Leave a comment

Your email address will not be published. Required fields are marked *