Copyright

SQL: Stored Procedures

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

Who said you can't write a program in SQL? A stored procedure is a powerful tool for creating a program that carries out SQL commands. This lesson will define the concept and provide examples.

SQL Stored Procedure

Stored procedures are basic programs - yes programs - written in SQL. They accept parameters and will carry out a specific task.

If you don't want to expose end users to the underlying statements but still want to give them power to run queries, stored procedures are very useful.

Another benefit is security. With sensitive data, such as financial, human resource, or medical information, you can't just give anyone access to tables directly. Running the stored procedures allows for querying, but also provides logs and history of the actions, so you can trace who was looking at what data.

Syntax

The basic syntax for creating a stored procedures is presented below. This is an example for SQL Server.


CREATE PROC [Procedure_Name]
[@parameter_here]
[parameter_data_type]
... [more parameters]
AS
[insert SQL statements here];


Stored procedures not only protect the underlying data, but they speed up queries and other functions. It takes time to write queries, and to get them to work just right. If you build a stored procedure that is just the way you need it to work, it is much easier to run the procedure than to rebuild the query.

Parameters

SQL stored procedures allow for parameters. That is, you can allow the end user (or other program) to input specific criteria for running the procedure. For example, if a stored procedure is built to query data for a certain payroll cycle, a parameter can be added to input the check date. The check date is passed into the SQL statements.

Parameters are very powerful because they allow queries and operations to be run not only repetitively, but with dynamic data as well. It would be painful to have to write new and complex queries every pay cycle! Why not create a stored procedure with some predetermined parameters?

The syntax for creating stored procedures is similar in SQL Server. But let's add on to our procedure by incorporating some parameters. Parameters are recognized by adding an '@' in front of their name.


CREATE PROCEDURE getEmployee
  @employeeID VARCHAR(50) OUT
AS
BEGIN
  DECLARE @employeeID INT;
  SELECT employeeID, payRate, hourlyWage, empStatus
  FROM tblEmployee
  WHERE employeeID = @employeeID
END;


In order to run the previous stored procedure with an employee ID, the following command can be used:


exec usp_getEmployee 'AB772';


Variables

SQL Server lets us create variables. While parameters are helpful for creating a dynamic set of instructions, variables take that one step further. We can create variables as a means for inserting data on the fly.

Variables follow the same formatting as the parameter, the '@' symbol. The basic method for creating the variable uses the DECLARE statement in front of the variable:


CREATE PROC updateEmployee
@EmployeeID VARCHAR
AS
DECLARE #runDate datetime
[insert SQL statements here];


Conditions

Just like a true program, stored procedures allow for conditional logic: IF EXISTS, ELSE, and BEGIN and END.

Continuing with the employee example, it is probably wise to check to make sure that the employee exists before performing updates! If the stored procedure tries to do something with an element that isn't there, it can cause serious errors, or completely stop running. This results in headaches for both end users and database administrators.

The next example is a little more complex, since it puts together the EXISTS statement with the parameters. First the code creates the parameter for the employee ID and a variable for the run date.

Next, it checks to make sure the employee ID even exists in the table. this is the IF EXISTS statement.

If the employee ID is found, the procedure carries out the statements in the BEGIN section: updating the employee table and setting the run date value. Otherwise, it completes the ELSE block and updates a temporary table.


To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support