How to Learn SQL Stored Procedures

Structured Query Language, or SQL, is the coding language of database development and administration. Once a database has been built and populated with data, the information stored within can be accessed and manipulated with statements written in SQL. Many times a data base administrator will find that they use the same SQL statement over and over. These frequently used statements can be written once and placed in a stored procedure, so they can be called quickly and easily with one command.

Instructions

Things You'll Need:

• Computer
• Populated database
• Admin access to the database

Create Your Stored Procedure

1. Step 1

   Write a SQL statement. SQL allows you to access and manipulate the data in a database with simple commands. For example, an INSERT statement allows you to add new data, an UPDATE statement allows you to modify existing data and a SELECT statement allows you to retrieve data. Decide what you want your stored procedure to do, and write a SQL statement to produce your desired outcome. Test your statement in the command window to ensure the results returned are what you expect, then save your statement in a notepad file.

2. Step 2

   Open the Stored Procedure window. Each database platform is a bit different, but every database that uses SQL will have an interface for adding stored procedures.

3. Step 3

   Add a comments section to your stored procedure. Comments are good coding practice because they add information about the coding intent and provide a place to log changes to the code made over time. Within your comments section include the name of your procedure, a description of the procedure, the date of creation, the author name and a section for changes. Comments must begin with /* and end with */. In your code window it will look like this.
   
   /*
   comments go here
   */

4. Step 4

   Create your stored procedure. SQL requires the following words for the creation of a stored procedure.
   
   CREATE PROCEDURE usp_storedprocedurename
   AS
   
   The prefix usp_ is the convention used in SQL to identify stored procedure names. It's not required, but it is a good idea. The name of your stored procedure can be whatever you like, but it is a good idea to give it a meaningful name that describes the procedure. The line below the create command will contain only the word "AS". This command tells the server to assign the code that follows to the stored procedure named above.

5. Step 5

   Add your SQL statement. Copy and paste the statement you saved in your notepad into the space below the "AS" command line. Save your stored procedure. To run your stored procedure, use the exec command in the command window as follows.
   
   exec usp_storedprocedurename
   
   You can make stored procedures for any SQL statement you like using this simple process