Skip to content

SQL 201 — CREATE PROCEDURE

2006 April 15
tags: ,
by Shannon Lowder

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.

5 Responses leave one →
  1. JustineL permalink
    June 9, 2012

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

    • June 11, 2012

      I’m happy the material has helped you. If you ever need any clarification, just let me know!

Trackbacks and Pingbacks

  1. Microsoft Exam 70-433 (part 2) | Shannon Lowder
  2. SQL201-The Syllabus | Shannon Lowder
  3. Studying for the 70-457:Revenge of the Sith | Shannon Lowder

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS