SQL databases let you create procedures to run code without retyping your logic. Stored procedures are one or more SQL statements that perform some action on the database. The action can be anything from creating a table, deleting data or retrieving data from your tables. Basically, stored procedures are short programs created for your database. If you retrieve data, a set of data is returned. If you just perform an action such as an INSERT, no data is returned. Any SQL action can be used in a stored procedure, even calling another procedure.
Elements of a Stored Procedure
The advantage of a stored procedure over writing standard SQL statements is that your procedures take arguments, so the data returned is dynamic. We used static arguments such as "WHERE state='tx'" where the state value does not change. But what if you wanted to create a program where you searched for customers in a state, but you wanted to send the SQL statement a state from your frontend code? You can perform this task using a stored procedure that takes a "State" argument.
The following two tables are used in our examples here.
Customer
|
CustomerId |
First_name |
Last_name |
City |
State |
|
321 |
Frank |
Loe |
Dallas |
TX |
|
455 |
Ed |
Thompson |
Atlanta |
GA |
|
456 |
Ed |
Thompson |
Atlanta |
GA |
|
457 |
Joe |
Smith |
Miami |
FL |
|
458 |
Frank |
Doe |
Dallas |
TX |
Order
|
OrderId |
CustomerId |
Total |
OrderDate |
|
1 |
321 |
10 |
1/2/2014 |
|
2 |
455 |
40 |
3/2/2014 |
|
3 |
456 |
20 |
3/10/2014 |
Using the above table, you can write a stored procedure that finds customers in a given state. The elements of a stored procedure are the variables used as arguments and the statements within the procedure.
The following code creates a procedure that returns a list of customers based on a "@state" variable.
CREATE PROC GetCustomersByState
(
@state varchar(2)
) AS
SELECT * FROM Customer
WHERE state=@state
The first part of this command creates the stored procedure. You only need the CREATE PROC statement when you first create the procedure. After you create the procedure, you just call that particular procedure by name, but we will go over calling a procedure in a bit. If you try to create the same procedure twice, the SQL database engine will return an error.
The next part of the CREATE PROC statement is the parameters or arguments. In this example, the stored procedure takes a "@state" argument. The @ symbol tells you that "@state" is a local variable. The variable is given a data type of varchar(2), so you can only pass a string value with two characters to this procedure.
The AS statement is followed by the SELECT statement used to retrieve data. The SELECT statement was something created previously to query customers in Texas. With this statement, you can use any state passed to the stored procedure. After the procedure runs, a list of customers is shown.
Running a Stored Procedure
After you create a procedure, you can run it any time from any part of your program. Another advantage of a stored procedure is that you no longer need to rewrite the same SQL statement. You just call the procedure.
Open your SQL engine and type the following code into your editor and run it.
EXEC GetCustomersByState ‘fl'
The above statement tells the SQL engine to run the procedure and passes "fl" to the statement as an argument. The results are the following data set.
|
CustomerId |
First_name |
Last_name |
City |
State |
|
457 |
Joe |
Smith |
Miami |
FL |
Procedures can take more than one argument. Suppose you want to perform a search for customers in a specific state and city. Instead of adding a new procedure, you just want to change the current one. Just like changing a table, stored procedures are changed using the ALTER statement.
The following SQL statement changes the stored procedure.
ALTER PROC GetCustomersByState
(
@state varchar(2),
@city varchar(50)
) AS
SELECT * FROM Customer
WHERE state=@state AND city=@city
The above statement adds the city parameter, which now takes a value of 50 characters. The parameter is then added to the procedure's WHERE clause.
The following code returns the same data set as previously shown, but the procedure takes two parameters.
EXEC GetCustomersByState ‘fl', ‘miami'
What if you want to use the same procedure, but sometimes you don't know the city and just want to pass a state. You could create two procedures, or you could add some logic to your stored procedure.
Look at the following procedure code.
ALTER PROC GetCustomersByState
(
@state varchar(2),
@city varchar(50) = NULL
) AS
IF @city IS NULL
SELECT * FROM Customer
WHERE state=@state
ELSE
SELECT * FROM Customer
WHERE state=@state AND city=@city
A few conditions have changed in the above procedure. The first change is the parameters. By setting a value in this section of the procedure code, you give the parameter or variable a default value. In this code, the default value is NULL.
Next, the IF statement uses some logic to run code depending on the value of the @city parameter. If the parameter is NULL, the procedure runs the code without the @city variable in the WHERE clause. If it is not NULL, the procedure runs the code with both the state and the city variables.
Now run the following SQL statement.
EXEC GetCustomerByState ‘fl'
The above statement uses NULL for the statement and returns the following data set.
|
CustomerId |
First_name |
Last_name |
City |
State |
|
457 |
Joe |
Smith |
Miami |
FL |
Now run the stored procedure again with the following parameters.
EXEC GetCustomerByState ‘fl', ‘tallahassee'
The above statement returns no records, because you don't have a customer in the matching city and state.
Procedures use any statement as long as these statements are valid SQL syntax. You can add a JOIN statement to your procedure to return a linked data set between multiple tables. For instance, you might want a list of customers with an order ID. You would then JOIN your customer table to the order table. Using the same stored procedure name and code, the following SQL statement would retrieve your data.
ALTER PROC GetCustomersByState
(
@state varchar(2),
@city varchar(50) = NULL
) AS
IF @city IS NULL
SELECT CustomerId, First_name, Last_Name, OrderId FROM Customer c
JOIN Order o ON c.CustomerId = o.CustomerId
WHERE state=@state
ELSE
SELECT CustomerId, First_name, Last_Name, OrderId FROM Customer
JOIN Order o ON c.CustomerId = o.CustomerId
WHERE state=@state AND city=@city
Now, you can run the procedure with the following code.
EXEC GetCustomersByState ‘fl'
The above code now returns the following data set.
|
CustomerId |
First_name |
Last_name |
OrderId |
|
456 |
Ed |
Thompson |
3 |
|
456 |
Ed |
Thompson |
3 |
Because there are two orders for the same customer ID, multiple rows are returned. You can eliminate the duplicate records by adding the DISTINCT keyword. Changing the stored procedure requires another ALTER command you can see below.
ALTER PROC GetCustomersByState
(
@state varchar(2),
@city varchar(50) = NULL
) AS
IF @city IS NULL
SELECT DISTINCT CustomerId, First_name, Last_Name, OrderId FROM Customer c
JOIN Order o ON c.CustomerId = o.CustomerId
WHERE state=@state
ELSE
SELECT DISTINCT CustomerId, First_name, Last_Name, OrderId FROM Customer
JOIN Order o ON c.CustomerId = o.CustomerId
WHERE state=@state AND city=@city
Removing a Procedure from the Database
In most database activities, you'll change or add procedures. You don't normally delete procedures, because unless you are sure it will not affect your programs. SQL gives you the ability to delete a procedure but it should be done with care just like deleting a table. Once the procedure is gone, you need to restore it from a backup if you change your mind.
The DROP keyword is used to remove a stored procedure from your database. Always ensure that you verify that dropping the procedure from your database won't harm any systems. You can do this by renaming the procedure and testing thoroughly in a development and staging environment. The DROP keyword removes the procedure with no warning, so you don't receive any kind of verification before it runs.
The following SQL statement shows you how to delete a procedure.
DROP PROC GetCustomeByState
That's it! The procedure is gone!
This article covers returning records and a little SQL logic within a procedure. You can use INSERT, UPDATE, AND DELETE statements in a stored procedure. Since procedures are small programs that affect a critical part of your system (your data), always make sure you thoroughly test your changes in a testing environment.
