If you know a little bit about SQL server, you might have been wandering why we have not spoke about stored procedures until now. Stored procedures are a powerful way to create reusable packages of SQL code that live in your database. We didn’t get into it earlier because stored procedures are basically just a wrapper for everything we’ve learned so far.

What Is A Stored Procedure?

To understand what a stored procedure does, think about what the shut down button of your operating system does. When you click it, the computer checks for any programs with unsaved work, makes sure nothing critical is running, then starts unloading memory, releasing memory and finally turning off the monitor and system power.

Everything you press this button, all of these steps have to happen. The same thing is true of lots of processes that involve SQL. For example, let’s consider a bank transaction. You need to move X amount of money from your savings to your checking account.

Stored Procedure
  1. Subtract X from the Savings Account Total.
  2. Add X to the Checking Account Total.
  3. Add a record of the transaction to a log table.

As you can see, this will take several SQL statements that must always happen in the same order. We also have a variable involved, X, for the amount of money. Also, we have to identify the SavingsID and CheckingID and the master AccountID that we’ll use. For now, let’s write the SQL as if that transaction was for $100 and the three IDs are 1. Later we’ll add in the variable.

UPDATE Checking 
SET Total = Total + 100
FROM Checking
WHERE CheckingID = 1

UPDATE Savings
SET Total = Total – 100
FROM Savings
WHERE SavingsID = 1

INSERT INTO Log (AccountID, AmtTransfered, CheckingID, SavingsID, CheckingChange, SavingsChange)
VALUES (1, 100, 1, 1, 100, -100)

This same SQL code will have to run every time we want to transfer money. Wouldn’t it be great if we could just plug in the values and not have to find all the occurrences in the code? It’d certainly make mistakes less likely!

Creating a Stored Procedure

Let’s start by encapsulating this SQL into a stored procedure. A stored procedure is created like a view using the CREATE statement. Once created, you can call it just like you would any other SQL statement. First, let’s talk about creating it:

Syntax:

CREATE PROCEDURE [procedurename]
AS
[any sql statements…]

Example:

CREATE PROCEDURE TransferMoneyToSavings
AS

UPDATE Checking 
SET Total = Total + 100
FROM Checking
WHERE CheckingID = 1

UPDATE Savings
SET Total = Total – 100
FROM Savings
WHERE SavingsID = 1

INSERT INTO Log (AccountID, AmtTransfered, CheckingID, SavingsID, CheckingChange, SavingsChange)
VALUES (1, 100, 1, 1, 100, -100)

Much like the view code, when you run this, no SQL runs, but instead a new object is built in the database. After running it, we can use the following code to move $100 between accounts.

TransferMoney

That’s all! We just took 11 lines of code and condensed them into a single statement. However, you’ve probably realized we’re not done! This procedure is too simple. It only allows you to transfer $100 between savings account 1 and checking account 1. We definitely need some variables in here to make this useful. But since we already created our procedure, we now need to either drop or modify the existing procedure.

Dropping or Modifying a Stored Procedure

WARNING: Be careful when it comes to dropping stored procedures. If you don’t have the SQL saved in a text file or in some other procedure, it’s gone for good!

Dropping a stored procedure from the database is just like dropping any other object. Let’s drop the one we just made.

DROP PROCEDURE TransferMoneyToSavings

Let’s recreate our stored procedure for transferring money. But this time, we’re going to add in variables that allow anyone who uses our stored procedure to fill in the important details like amount of money and account identifiers.

Syntax:

CREATE PROCEDURE [procedurename] (variableName variableType, ..)
AS

[any SQL…]

Example:

CREATE PROCEDURE TransferMoneyToSavings
(@Amount money, @AccountID int, @CheckingID int, @SavingsID int)
AS

UPDATE Checking 
SET Total = Total + 100
FROM Checking
WHERE CheckingID = 1

UPDATE Savings
SET Total = Total – 100
FROM Savings
WHERE SavingsID = 1

INSERT INTO Log (AccountID, AmtTransfered, CheckingID, SavingsID, CheckingChange, SavingsChange)
VALUES (1, 100, 1, 1, 100, -100)

Now the users are able to supply the needed information to our stored procedure. But we also need to replace all the hardcoded values in the SQL code. Let’s do that thru an ALTER statement. It works just like the CREATE but it updates the existing SQL.

ALTER PROCEDURE TransferMoneyToSavings
(Amount money, AccountID int, CheckingID int, SavingsID int)
AS

UPDATE Checking 
SET Total = Total + @Amount
FROM Checking
WHERE CheckingID = @CheckingID

UPDATE Savings
SET Total = Total – @Amount
FROM Savings
WHERE SavingsID = @SavingsID

INSERT INTO Log (AccountID, AmtTransfered, CheckingID, SavingsID, CheckingChange, SavingsChange)
VALUES (@accountID, @amount, @CheckingID, @SavingsID, @amount, -(@amount))

Now when someone wants to use this stored procedure, it is truly dynamic.

TransferMoneyToSavings 1, 50, 2, 2, 3
TransferMoneytoSavings 1, 5000, 2, 3, 1

They can make as many transfers as they want. However, we might want to put some rules in place. For example, should someone be able to transfer $5,000 when you only have $50? That’d be nice, but it’s not practical. In the next lesson we’ll learn about how to add checks and controls to our stored procedures.

Categories : SQL
Tags : t-sql, stored procedure, SQL Basic


author's photo

Author : SQL Tutor

Tainyan staff who teach SQL.

COMMENTS
blog comments powered by Disqus
Contributors
Categories