Chapter 19 - Stored Procedure Functions

“Freedom from effort in the present merely means that there has been effort stored up in the past.”

- Theodore Roosevelt

Stored Procedures Vs. Macros

Stored Procedures are a lot like Macros. However, they manipulate data a row at a time. Stored Procedures take up PERM Space, unlike Views and Macros that do NOT. Stored Procedures are actually compiled, and will use a Cursor to retrieve or manipulate more than one row. Although, Stored Procedures utilize SQL they also utilize SPL, which stands for Stored Procedure Language, which provides loops, while, etc.

Creating a Stored Procedure

THE BEGIN and END statements are required in all Stored Procedures. Don’t miss a semi-colon. They are everywhere. I have bolded them for your convenience.

How you CALL a Stored Procedure

You SELECT from a View, EXECUTE a Macro, and you CALL a Stored Procedure.

Label all BEGIN and END statements except the first ones

When you have multiple BEGIN and END statements, you have to label them all (except for the first BEGIN and END statements). We have labeled our next set of BEGIN and END SecondSection.

How to Declare a Variable

When you DECLARE a variable and then reference that variable later, a colon is always in front of the Variable.

How to Declare a Variable and then SET the Variable

Once a variable and the data type is defined the value must be assigned. SET is the more flexible a method compared to DEFAULT.

An IN Variable is passed to the Procedure during the CALL

The Variable Var1 was not assigned with the DEFAULT or the SET, but instead passed as a parameter. There are three types of parameters (IN, OUT, INOUT). In this example, an IN is being used. Warning: You cannot add, subtract, or change an IN variable. You set it when you call the procedure and that value remains constant.

The IN, OUT and INOUT Parameters

There are three types of parameters (IN, OUT, INOUT). This is an example of an IN and an OUT parameter. What that means is this Stored Procedure will take a parameter in and then spit something out. Notice that we named the OUT parameter Msg and then we needed to put the name Msg in our Call statement.

Using IF inside a Stored Procedure

Msg          

They are equal

Why did the Msg say “They are equal”? Because both Var1 and Var2 were the same value.

Example of two Stored Procedures with different techniques

These queries do the SAME thing. However, the first one is more efficient because it only does TWO calculations instead of three.

Using Loops in Stored Procedures

LOOPs require Labeling. Much like when you have more than one BEGIN/END.

Using Keywords LEAVE vs. UNTIL for LEAVE vs. REPEAT

Both Procedures above do the same thing. The UNTIL keyword in Procedure Two jumps it out of the REPEAT Loop. There are some differences in the above. The first example (Procedure One) tests Cntr before the INSERT, but Procedure two does not so Procedure two will always do at least one INSERT no matter what Cntr is set at.

Stored Procedure Workshop

Your mission is to create the table above and then create a stored procedure that will insert 1,000 rows. The tricky part is that col1 should have 1,000 unique values, but col2 should have only 250 different values.

Stored Procedure Workshop Answer

Your mission was to create a table and then create a stored procedure that will insert 1,000 rows. The tricky part was that col1 had 1,000 unique values, but col2 had only 250 different values. This is the way we did it.

Stored Procedure Workshop Alternative Answer

Your mission was to create a table and then create a stored procedure that will insert 1,000 rows. The tricky part was that col1 had 1,000 unique values, but col2 had only 250 different values. This is another way we did it.