It is not a trivial matter to build the sort of multi-tier, web application that every corporation seems to want. It is too easy to end up with a morass of extremely complicated code that is difficult to get working correctly even with extensive testing, and it can furthermore be quite challenging to maintain it in production.

T-SQL Stored Procedures (SPs), along with some views and functions, are a useful way to encapsulate and implement most of an application’s business logic, especially that which retrieves the underlying data from the tables (master or transaction), and/or updates it.  This is useful because:

•  It allows the database developer to work fairly independently of the application developers.
• It means that the application developers could concentrate their efforts on getting the best possible user-interface without needing to understand the relational design of the base tables within the database.
• It provides a logical abstraction of the database that could be ‘mocked up’ to assist with testing.
• It allows a much more robust security regime for the database that reduces the likelihood that SQL Injection can occur.

I will describe two basic templates that can be used for many commonly-seen forms and web pages, which can then be elaborated as required. 

Occasionally in this article I will refer to an application that my company recently and successfully developed using this approach, along with the reasons why it was successful.  Without going too far into the details, I can say that in my 7+ years of experience with this company, this application ranks within the top three or four in complexity among all of those I’ve been intimately involved with.  It was the culmination of refining the approach I will describe, and it was successful because it was delivered with minimum staffing, on-time and within budget.  While it has not been in production long enough yet to fully assess customer satisfaction levels, initial reception has been promising and that is no mean feat considering it was designed to replace a legacy system that had been in use for over twenty years.

Now that I’ve established those rather lofty expectations, let’s start by explaining the basic idea within a common business requirement.

The Header/Details Form Pattern

I’ll start by describing, as an example, a relatively simple but common business document, an invoice, after which I’ll flesh out the T=SQL that would implement its’ functionality.

There are two parts to an invoice: header and details.

In the invoice’s header, you’ll normally find at least the following information

•  An invoice number, which is often the primary key in the underlying table and is usually system-generated.
•  The invoice date.
•  The customer to which the invoice will be sent.
• That customer’s billing details.

There may also be other sundry information that you may think of as being part of an invoice’s footer, because they appear at the end of the physical document once printed. In reality, these are stored with the header.  This does not include the invoice total, which is derived by summing the details.

In the invoice’s details, there will be:

• A line number, or some other way of sequencing the detail lines of the invoice and which uniquely identifies each detail line as unique.
•  An item code (the product description would normally be retrieved by joining the item code back to the item table).
•  Quantity of the item.
•  Price of the item (unit price).
•  Extended price, or the product of quantity and price which, being derived from them, need not be actually included in the table.

Here is an example of a typical User-interface form that could be designed to create/maintain an invoice.

There are several buttons on this form, each of which represent an action that the user can take.  This form would initially be in “query mode,” meaning that the user enters the filtering criteria that are required to retrieve the unique invoice record and then presses ‘Execute Query’.  The ‘Query‘ button returns the form to the ‘query mode’.  Alternatively, the ‘Insert‘ button may be used to create a new invoice, with the invoice number being auto-generated upon clicking ‘Save‘.  We’ve also offered our user the means to ‘Delete‘ an invoice

In the ‘Invoice Details’ section, users have the options to update or delete an existing row (U and D buttons), or insert a new row, all through the action buttons provided.  Most modern web applications will replace the buttons with cute icons, with context-sensitive help text that appears as you hover the mouse over them, but the concept remains the same.

The shaded patterns on the form represent text-panels where error messages may be displayed, so you see one panel below the action buttons, for errors that occur when processing the header, and also one above the grid for errors on actions on the details.

Let’s also mention the basic business rules for using the form.

• In order to query (retrieve) an invoice, that invoice’s number must be supplied.
• To create a new invoice, the customer ID must be entered before pressing the ‘Insert’ button, so that the existing customer details can be used to populate the relevant default values on the form.
• The invoice number for a newly-created invoice is generated when it is first saved.  The format is “IN” plus the year of the invoice, plus a running number that must be unique.
• The user may change the billing information (e.g., the customer name, address, etc.) with those changes to be applied to the single invoice only.

Implementing Form Actions through SPs

We can develop two Stored Procedures (SPs) that will together support the functionality of this form.  The first is implemented to perform all of the allowable form actions (buttons) for the header record, including a cascade delete of the details/header if that is required.  The second, somewhat simpler, SP is developed to support maintenance of the details.  Even though the ‘details’ SP is simpler, it will follow the same template or pattern as for the header SP.  We will discuss the differences later.

The design of the SPs is intended to insulate those who are developing the forms from needing to know about the details of the physical implementation of the data in the relational tables.

This will allow DBAs to set access rights to allow only execute privileges on SPs, giving access to the data indirectly via ownership chaining, without having to assign rights to insert, update, delete, etc. directly against the tables (or views).  This effectively prevents almost all possibility of SQL Injection attacks.

So far, other than my rather vague statement that the SPs “implement each of the form’s actions,” I haven’t mentioned precisely what the SPs will actually do.  This should become clear as we review the template in the next section, but one thing that I’ll mention right now is that the SPs must return something.

For our invoices form, the header SP should return three tables:

• What I will call the “ReturnErrors” table, which delivers results from audits performed in the SP.
• A table consisting of zero or one row with columns of data that represent the header information, and any additional derived data based on that.
•  A third table that consists of the invoice details, with columns that are consistent with the data appearing in the details grid.

If you are using .Net as your development environment, you can make that front-end code retrieve data from all three of the tables mentioned above (or more if needed).  I will not go into the details on how this is done, because I’m not a .Net developer. Suffice it to say that a dataset or OdbcDataReader will store all tables returned by a stored procedure in one action.

An Invoice Header SP Template

Below we have a template that can be used generically for any “header” type set of actions such as defined for our form, created specifically to maintain our invoice header.  There’s a few comments interspersed with the code to help you follow along and a more detailed description at the end.

Let’s now describe the relevant components of our SP template:

1.       The parameters passed to the SP consist of an @action variable (required) and “optional” parameters for each column in the header table.  Some will not really be optional, but required based on the action that is being performed (validated in the SP).

2.       There’s a section to define some local variables and also a table variable that has the same columns as our header table, but no primary key.  The attribute on the primary key column (all columns in fact) must allow NULL values.

3.       There’s a section to cleanse the input parameters.  Mainly this consists of setting blank values to NULL, in case the forms developers weren’t particularly thorough in passing NULLs when it makes sense to do so.

4.       The auditing section, which consists of three parts:

5.       Then there is the “actions” section, also broken into three parts:

6. Finally, we return the three tables that we discussed above. Naming each table something relevant is one way of retrieving the tables using .Net.

Notice also how we perform the audits only until one of them fails (IF @Error_Code = 0 AND …) and do not perform the actions unless all audits pass.  We have omitted the invoice total, which would be simple enough to add as a calculated column to the header table as the sum of the details.

We’ll show some results of testing  for this SP in the next section.

Some Test Data and Initial Testing Scripts for the Header SP

Thanks to the wonders of deferred name resolution, we could have already created the above SP without the underlying tables being present.  You can run the script below to create the tables which we will use to demonstrate the actions of the header SP.

Note that while you may take exception to some of my chosen data types, and the simplifications that I made regarding prices appearing on price lists and selling units of measure, please indulge me as we’re not here today to demonstrate how best to structure such data.

The DROPs are provided as a ‘teardown’ code to help you clean up your sandbox once all is said and done.

Let’s now look at some test scripts and their results.

We’ll leave it to our interested readers to verify that all audit rules trigger the correct error messages when an audit fails.

An Invoice Details SP Template and some Test Scripts

As noted earlier, the Invoice details SP is much simpler than for the header.  However it is still based on a trimmed-down version of the header SP’s template.

You may notice that, even though there is only one MERGE statement within the TRY/CATCH block (i.e., no other MERGE, INSERT, UPDATE or DELETE statements), we’ve still included the TRANSACTION in our template because there could very well be more statements if required by the business logic.

The main difference between the details and header template, is that the details template only returns the one table (ReturnErrors) for use by the front end application.  Also it is not necessary to use a table variable to store the details in.  After the details SP is executed, the form simply re-executes the header SP in search mode to recapture any changes to the details (and possibly the header record itself).  The forms developer must then refresh this information to the fields in the form.

Now let’s test the available actions for the details (from running the test scripts above we now only have invoice number: IN20141000002 available).

Notice how in each case we’re selecting all of the rows in our invoice details table to see the effect.  The forms developer would simply rerun the header SP in query mode for the invoice, then pick up the results returned for the details table and refresh that into the grid.

The first results pane shows the results from calling the details SP.  The remaining three represent the results from rerunning the query against the header record.

Business Logic Left to the Presentation Layer

The forms developers will still need to implement some specific business logic in the presentation layer where certain actions should be enabled or disabled. For example:

•  Enabling/disabling buttons at various points in the process wherever it makes sense to do so: For example, the Save button should probably only be enabled once an invoice has been retrieved (or the Insert stub is displayed).
•  Any role-enabled functionality probably requires some work by the forms developer to operate the form correctly.  For example, suppose there are two types of users that can access the form: normal and super-users.  Perhaps only super-users are allowed to delete an invoice.  The SP should perform this audit, but a more user-friendly forms behavior is to simply disable the Delete button if the user is not a super user.
•  Normally, once customer ID is selected and the Insert button pressed, you don’t want the user changing the customer ID, so making that field display-only probably makes sense.
•  After the user performs the Execute Query action for an invoice and that returns an invoice successufully , you’ll want to make the Invoice number field display-only, because it makes no sense to change the invoice number since they are being auto-generated by the header SP.

It is also possible to use the SP itself to flag when some or all of these specific conditions apply.  This can be done by adding additional columns to the ReturnErrors table’s results and then using these as control flags in the front-end code.

If you will follow the strict advice of your DBAs and limit developers’ direct access to tables and views, you’ll need to take one more step.  For cases where forms populate a lot of dropdown lists, you can write cover FUNCTIONs that return the needed results.  We tend to use schema-bound, in-line Table Valued Functions for best performance.

Some of the Benefits of the Steps Described Above

The table below summarizes some of the additional benefits to using this approach.

No.	Development Phase	Benefit and Remarks
1	Analysis and Design	A simple event-driven model that is the same can be developed for each form based upon the actions to be delivered by the SPs and the user actions (buttons) expected on each form.
2		A skilled SQL Developer should probably be able to translate a requirements specification directly into audits required for a particular form, without the need for an intermediate program specification.
3	Coding and Unit Testing	Forms developers are freed from having to understand the complex business logic underlying the application and can focus on forms behavior and usability improvements.
4		Only essential logic needs to be coded into the SPs on the first pass.  Incremental development of the SPs can add the rest.
5	Miscellaneous Testing	As the SQL Developer writes test scripts, they can be saved as part of the SP documentation, thus allowing any testing team capable of white-box testing to generate many variants on the calling parameters to the SP to ensure defect-free behavior of the SPs.
6		Once forms-development has progressed to the point that they are calling the SPs correctly, using SQL Profiler to trap the SQL generated by the form can allow the creation of multi-step scripts so that complex transactions can be executed repeatedly.  We’ve used this successfully to generate hundreds of test transactions, within a SQL WHILE loop by simply varying some of the input parameters or assigning random values to them.
7	Documentation	Documentation of the SPs need only be minimal: The call signature (where parameter data types represent the underlying data type for the database tables). The returned errors, clearly denoting which audits should never fail when the SP is called correctly, which should be done in the form and which should be done only by the SP. The columns returned by each of the tables (and of course the table names) returned by the SP.
8	Performance	Because SPs are compiled and executed as a block of SQL code, they tend to be a little better performing than making multiple database connections and executing individual queries.
9		Eliminating round trips from application to the database.  It is especially crucial to minimize trips from the client, to the application tier and then to the database because of the latency inherent in such a trip.  It is also beneficial in reducing the round trips from the application server to the database server, as each time you make a connection and then close it, it generates latency.
10	Maintainability	Deployment effort was significantly reduced in cases where a fix involved only a change to one of the SPs.  That can be done as an incremental deployment with no need to ask all users to log out while the web application itself is deployed.
11	Security	Through proper controls within the SP, you can avoid issues with SQL Injection.

Some additional information on point #6.  There are automated testing tools that allow complex transactions to be scripted through an application’s forms.  However, these tend to be expensive, and do not eliminate the requirement for some scripting.  In our case, scripting in SQL is probably a much more widely known skill than writing scripts for an automated testing tool.

Managing the Development Process

One of the primary roles of a project manager is to select the right team to execute the project.  This is most certainly true when selecting the person for the role that we’re calling the SQL Developer.  That is the person that will construct the SPs that enable this method to work.

Some very specific skills are required.  This person must have:

•  A deep technical expertise in the dialect of SQL being used, specifically a wide breadth of knowledge of SQL and high-performance code patterns.
• The knowledge to translate a logical data model into the physical implementation, and the skills to create all of the necessary SQL objects (tables, views, etc.).
• The ability to develop and test quickly, and deliver reasonably defect-free SPs to support at least three or four developers who will be working on the forms.
• The ability to make logical decisions on what to include, or what can reasonably be excluded, in the initial SP implementations, because it may be necessary to develop the SPs incrementally so as to stay ahead of the development team.
•  The ability to translate (possibly) an incomplete or unclear requirements specification into firm audit rules to be implemented in the SP.
• The thoroughness to document the SPs so that the developers can use them during forms-development. 

I have found that this documentation need only be sufficient to serve its purpose: the SP’s call signature, the errors returned and the columns returned in each of the SPs tables, along with some test scripts that can be run to verify that the SP is actually working.  This thoroughness must extend to the data types for the parameters being passed to the SP, because these should be identical to the column data types in the underlying tables.  Providing information to the developers on which of the audits are “simple data audits,” and thus should be performed within the form’s logic is also useful.

In reality of course, the code should also be well-written and commented, to improve troubleshooting, maintenance and the incremental development effort that will go into making the SPs fully functional.  The idea to “Make it Work, Make it Fast and then Make it Pretty” comes into play here, however to avoid Donald Knuth’s statement that “Premature optimization is the root of all evil,” my recommendation is to leverage best-practices and high-performance code patterns to make it “fast enough” at the outset.  For more information on what Knuth’s quote does not mean, read this article by SQL MVP Gail Shaw: Premature Optimization.

It is probably not sufficient to “temporarily divert” a .Net developer to be the SQL Developer, because unless they are very senior they’ll probably fall short in one or more of the areas mentioned above.

The project manager must plan to give the SQL Developer a head start on the development team.  Probably at least a week should suffice.  After that head start, developers can be scheduled to begin working on visual forms design tasks.  By the time that is complete, there should be sufficiently functional SPs available to begin making the forms work.

For estimation purposes, you can expect that initial development of an SP like the one above for headers should be ready in about 0.5 – 1.0 man-day including documentation.  Details SPs should be ready in just a few hours.  Note that this is the basic effort to get them ready for the developer to start.  The overall effort is probably double that to make them fully functional and defect-free, and more complex forms and business requirements may take some additional time.

As the SQL Developer completes his work, it is a good idea to turn over his test scripts to a testing team for some “white box” testing across different scenarios.

The SQL Developer should also maintain a “to-do” list of outstanding, uncompleted items, which can be reviewed periodically and prioritized for implementation.  The project manager should help to prioritize the list, based on progress by the forms-development team.  First priority for the SQL Developer, of course, will always be to fix any issues identified by the forms-development or testing teams, so that his effort is not blocking progress of the forms-development. 

While not specifically discussed in this article, SPs (or views) can also be part of the job of the SQL Developer for reports delivery.  Once again, because reporting requirements may involve complex SQL (and require high-performance), the SQL Developer may be better suited to the task than reports developers.

You may be thinking that it is impossible for one SQL Developer to stay sufficiently ahead of a three-person forms-development team, but I can assure you that it is not impossible because it was done in our reference project with time to spare.

Reflections and Final Commentary

There were a few variants that came up in our development project, which were relatively easy to handle:

•  Some forms had multiple query criteria, each of which would return a unique row of the underlying table; usually the header table.  This was handled by using a dynamic search stored procedure as recommended by SQL MVP Gail Shaw in her SQL-in-the-Wild blog: Catch all queries.
• Some of the forms actually had multiple secondary (details) tables, where one grid may be visible at a time and the user was allowed to switch between the grids.  That’s another case of some special logic built into the form, but, for that case, the header SP returned more than three tables.
• Using SQL Profiler and white-box testing, it was possible to ensure than every audit that should be done in the form, even if done redundantly in the SP, did not invoke a call to the SP.  The overhead of redundancy in the SP tends to be pretty minimal from a performance perspective, so long as it is limited to audits that don’t check against the database.
•  When bugs did show themselves in the SPs, because they were well-written, organized and always followed the same template, the bugs were usually identified and fixed within about fifteen minutes.
• As we were testing, none of the forms transactions seemed to respond in an inordinate amount of time (to me that means more than three seconds).  So basically the performance of the resulting forms was at least acceptable, even when the update actions might hit five or more different tables. and we confirmed this with the user during User Acceptance Testing,
• In our application, we were careful to include inserted by/date/time and updated by/date/time on every single record in every header and detail table.  All this was handled within the MERGE statements, and the only requirement imposed on the forms developers was to pass into the SP the login name of the user of the form.

I seriously recommend you try this approach because of the benefits we found to the development of our reference application.  It would not have been possible to deliver that level of business logic complexity within the timeframe that we did without using this approach.

Naturally during the initial week after Go Live, some issues were identified.  We were able to rapidly deploy modifications to the business logic by deployment of a single SP.  Regardless of whether this was to correct a bug or improve how the application was able to deliver results to the users, the users’ satisfaction with how quickly those changes could be implemented was frequently communicated to us.  In this case, the users’ business was very time-critical, so deployments of the entire web application requiring locking the users out for a period of time needed to be avoided as much as possible.  The first application deployment after Go Live was not required until after the first week of use.

During that week of Go Live, we also were intensely focused on performance.  The users we were observing were accessing the application over their Intranet, and most form actions were responding instantaneously, with just a few of the more complex search operations taking one to two seconds.  While you would not expect remote users of the application to get quite those same performance results, most of the business is conducted at the Go Live site, so our expectations are that performance of the application will meet their needs.