All Things New

Digg This!

Page 2 of 2   « previous page

The code for our stored procedures can be found in Listing 1. Please note that I've purposely excluded all error checking. We can revisit this when we talk about transactions and error handling.

There are two differences related to the Users table that we need to account for. First, we won't display the password values from the User table. We'll come up with a different mechanism to let the user or administrator change passwords. Second, we have to allow the key value, userid, to change. This will be reflected in the update stored procedure we'll write - please take note.

Regarding the naming convention to be used for our stored procedures, we'll use the following forms for the following functions:

• get_<table>_list will select all column values for all rows with no arguments.
• get_<table> will select all column values for a key-value argument.
• insert_<table> will insert a new row into the database table with all values provided, including key values.
• update_<table> will update a row in the table with all values provided including key values which will be used to find the row to update. This assumes the key values will provide a unique key that is something every table should have.

It's very common to see insert and update logic combined in a single stored procedure. This is implemented by including an "if exists()" check to see if the row already exists in the table. If it does, an update statement is executed; if not, then an insert statement is executed instead. Typically, there's very little benefit in doing this and I would discourage it strongly unless there's a strong case for doing so. One such case might be a feed processor that requires the "if exists()" check for every record fed. In this situation, it may be cleaner to have the processor call the insert/update procedure with all the arguments and let the procedure check for the existence rather than forcing that check into the processor's codeline.

In the same way, the get_<table>_list and the get_<table> procedures could have been replace with a single procedure to which either a valid key value or null would be passed. If your database supports the function isnull() or something similar, then the where clause would include the phrase:

<column> = isnull(argument, <column>)

If the argument isn't null, the argument value is used as is and compared to the column value. If the argument is null, the value is replaced with the column value itself. This means the where clause would include the phrase

<column> = <column>

which always evaluates to true. Hence, if a null argument was passed, all rows would be returned; otherwise the row that matches the argument value would be returned.

In more complex applications, there may be any number of variations of stored procedures for selecting, inserting, updating, and deleting just as there may be any number of variations for presenting the user with ways of searching, viewing, and editing. Since our application is fairly simple, we can afford to, and should, take the simplest approach to meet our specifications and objectives. As we progress to more complex applications, we will, by necessity, be presented with additional challenges that we'll face and overcome with more complex solutions. However, we must always endeavor to create the "simplest" complex solutions and not get carried away or lose sight of the goal of creating a fully functional, robust, understandable, and maintainable applications.

Page 2 of 2   « previous page