Stored Procedures

Excerpt from Beginning VB.NET Databases

By Thearon Willis

A stored procedure is a group of SQL statements compiled into an execution plan and stored under a unique name in the database. It is then executed as a unit. Stored procedures in SQL Server and Oracle are similar to queries in Access. However, stored procedures are much more flexible and powerful than queries, as you'll discover in later chapters when you start implementing more complex stored procedures.

Each database vendor provides tools that enable you to work with the objects within their databases and to view and create stored procedures. For example, Microsoft provides the Query Analyzer for SQL Server, whereas Oracle provides SQL Plus and SQLPlus Worksheet for Oracle.

However, Visual Studio .NET also provides tools for viewing and creating stored procedures in SQL Server and Oracle. Although these tools are not as robust as the database vendors' tools, they do the job and enable you to develop database applications without having to leave the development environment.

Before you begin using Visual Studio .NET to develop stored procedures, you'll need to take a look at the basic syntax for creating stored procedures in SQL Server and Oracle. The following example is a basic stored procedure that will insert group data into the Groups table in SQL Server:

CREATE PROCEDURE usp_InsertGroup
(
   @GroupID           UNIQUEIDENTIFIER,
   @GroupName         VARCHAR(50),
   @GroupDescription  TEXT
)
AS

INSERT INTO Groups 
   (GroupID, GroupName, GroupDescription, LastUpdateDate)
   VALUES(@GroupID, @GroupName, @GroupDescription, GETDATE())

The INSERT statement for this stored procedure is similar to the INSERT statement that you created for your usp_InsertGroup query in Chapter 7. The only difference is instead of using the built-in Access function to insert a date and time into the LastUpdateDate column, you are using the built-in SQL Server function GETDATE().

When you built your Access query, you didn't need to specify the input parameters to the query or their data types. That is inferred by Access, and Access will prompt you for them when running the query within Access. When you ran your query from your VB.NET program, you knew what parameters to specify and the order in which to specify them based on their use in your INSERT statement. You also knew their data types as specified in the column definition when you created the tables. This is not the case with stored procedures.

You create a stored procedure with a CREATE PROCEDURE statement and then specify the input and/or output parameters to the stored procedure. You also specify the parameter data types as you want them submitted to your stored procedure. A case in point is the example stored procedure listed previously.

Another important point regarding input parameters and variables in general when dealing with SQL Server stored procedures is that the parameters and variables must begin with an at (@) sign as shown in the previous example. This identifies the parameters and variables as variables local to the stored procedure, unlike SQL Server's global variables, which begin with two at signs and are global to all stored procedures.

Let's examine the syntax of the previous example that creates a stored procedure for SQL Server. You must specify the statement CREATE PROCEDURE to create a new stored procedure in SQL Server. This is followed by the stored procedure name, which can be up to 128 characters in length. You then specify the parameters for the stored procedure and their data types. Enclosing the parameters in a set of parentheses is totally optional in SQL Server but will be done here to be consistent with Oracle, which does require them.

Each parameter begins with an at sign (@) followed by the name of the parameter. Each parameter has the data type of the parameter specified, and if the data type allows a varying number of characters or digits, that must also be specified. For example, the @GroupName parameter in the previous example specifies a data type of VARCHAR. Because this data type allows a varying number of characters, you must also specify the maximum number of characters that will be allowed in the parameter.

After you have specified the CREATE PROCEDURE statement, the stored procedure name, and the parameters, you specify the AS keyword followed by the body of the stored procedure. The body of the stored procedure will be made up of one or more sets of SQL statements, such as the INSERT statement. The VALUES clause of the INSERT statement shown in the example contains the parameters as the source of input for the INSERT statement.

Now that you've had a look at how to create stored procedures in SQL Server, turn your attention to how a stored procedure is created in Oracle. An example of the usp_InsertGroup stored procedure for Oracle is shown here for reference:

CREATE OR REPLACE PROCEDURE usp_InsertGroup
(
   inGroupID            CHAR,
   inGroupName          VARCHAR2,
   inGroupDescription   CLOB
)
AS
BEGIN
INSERT INTO Groups 
   (GroupID, GroupName, GroupDescription, LastUpdateDate)
   VALUES(inGroupID, inGroupName, inGroupDescription, SYSDATE);
END;

In Oracle, you specify the CREATE OR REPLACE PROCEDURE statement to create a stored procedure, followed by the stored procedure name. You should note that a stored procedure name in Oracle is limited to 30 characters.

Next, you must specify the stored procedure parameters inside a set of parentheses. Each parameter is given a name, followed by the data type of the parameter. Note that an at sign (@) is an illegal character in Oracle so the parameters in the example above have been prefixed with the word in to indicate that these are input parameters. Also note that you do not specify the size of the data type for data types that allow a varying number of characters.

After you have specified the parameters for the stored procedure, you specify the AS keyword and then specify the BEGIN clause. This is followed by the body of the stored procedure, and the BEGIN clause is terminated with a matching END clause and a semicolon.

The body of the stored procedure in this example contains the INSERT statement, which you have become very familiar with. The VALUES clause for this INSERT statement contains the parameters for the stored procedure as the source of input for the INSERT statement. The complete INSERT statement is terminated with a semicolon. It is important to note that every SQL statement in Oracle must be terminated with a semicolon character.

As you can see from the two previous examples, the stored procedure in SQL Server is not all that different from the stored procedure in Oracle. The INSERT statement is the same and uses the parameters for the stored procedure as the source of input for the VALUES clause. There are very minor differences in the syntax for creating a stored procedure in SQL Server and Oracle, as you saw in the previous examples.