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.<\/p>\n
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: <\/p>\n
- \n
- It allows the database developer to work fairly independently of the application developers.<\/li>\n
- 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.<\/li>\n
- It provides a logical abstraction of the database that could be ‘mocked up’ to assist with testing.<\/li>\n
- It allows a much more robust security regime for the database that reduces the likelihood that SQL Injection can occur.<\/li>\n<\/ul>\n
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. <\/p>\n
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.<\/p>\n
Now that I’ve established those rather lofty expectations, let’s start by explaining the basic idea within a common business requirement.<\/p>\n
The Header\/Details Form Pattern<\/h2>\n
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. <\/p>\n
There are two parts to an invoice: header and details.<\/p>\n
In the invoice’s header, you’ll normally find at least the following information <\/p>\n
- \n
- An invoice number, which is often the primary key in the underlying table and is usually system-generated.<\/li>\n
- The invoice date.<\/li>\n
- The customer to which the invoice will be sent.<\/li>\n
- That customer’s billing details.<\/li>\n<\/ul>\n
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.<\/p>\n
In the invoice’s details, there will be:<\/p>\n
- \n
- A line number, or some other way of sequencing the detail lines of the invoice and which uniquely identifies each detail line as unique.<\/li>\n
- An item code (the product description would normally be retrieved by joining the item code back to the item table).<\/li>\n
- Quantity of the item.<\/li>\n
- Price of the item (unit price).<\/li>\n
- Extended price, or the product of quantity and price which, being derived from them, need not be actually included in the table.<\/li>\n<\/ul>\n
Here is an example of a typical User-interface form that could be designed to create\/maintain an invoice.<\/p>\n
![\"2097-clip_image002.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2097-clip_image002.jpg\")
<\/p>\nThere 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’<\/i>. The ‘Query<\/i>‘ button returns the form to the ‘query mode’. Alternatively, the ‘Insert<\/i>‘ button may be used to create a new invoice, with the invoice number being auto-generated upon clicking ‘Save<\/i>‘. We’ve also offered our user the means to ‘Delete<\/i>‘ an invoice<\/p>\n
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.<\/p>\n
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.<\/p>\n
Let’s also mention the basic business rules for using the form.<\/p>\n
- \n
- In order to query (retrieve) an invoice, that invoice’s number must be supplied.<\/li>\n
- 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.<\/li>\n
- 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.<\/li>\n
- 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. <\/li>\n<\/ul>\n
Implementing Form Actions through SPs<\/h2>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
For our invoices form, the header SP should return three tables:<\/p>\n
- \n
- What I will call the “ReturnErrors” table, which delivers results from audits performed in the SP.<\/li>\n
- 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.<\/li>\n
- A third table that consists of the invoice details, with columns that are consistent with the data appearing in the details grid.<\/li>\n<\/ul>\n
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<\/a> or OdbcDataReader<\/a> will store all tables returned by a stored procedure<\/a> in one action.<\/p>\n
An Invoice Header SP Template<\/h2>\n
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.<\/p>\n
CREATE PROCEDURE [dbo].[Maintain_Invoice_Header]\r\n-- =============================================================================\r\n-- Author: Dwain.C\r\n-- Create date: 15-Nov-2014\r\n-- Description: This is a template for an SP that maintains the header of an \r\n-- invoice document. \/* Comment blocks *\/ are explanations of the \r\n-- sections of the template and should be removed in a customized \r\n-- version of the template.\r\n-- =============================================================================\r\n(\r\n\/*******************************************************************************\r\n ** Parameters Section **\r\n \r\n Which includes:\r\n - An @action variable that corresponds to form actions (buttons), along with\r\n any other parameters such as the user that is performing the action, \r\n which could be used to maintain a record of the person performing the \r\n change.\r\n - The filtering criteria (in this case @InvoiceNo), which could be more than\r\n one form field, in which case either parameter must return a unique \r\n result.\r\n - The data values (form fields) which will be updated on that action. Note \r\n that this includes @InvoiceNo, which is required on save (if the invoice \r\n exists) and delete.\r\n \r\n After the @action variable, default values for all parameters are defaulted \r\n to NULL, because the SP will validate (audit) that appropriate values are \r\n provided on each specific action.\r\n \r\n It is a best practice to call this SP using specified parameter names rather \r\n than assuming that the positional placement won't change.\r\n \r\n Note that data parameters (including @InvoiceNo) should be identical to the \r\n data types for the underlying table's columns, so that when you publish the \r\n SP's call signature the developers know how that data is stored in the \r\n front-end code.\r\n*******************************************************************************\/\r\n -- @action=0 Query, =1 Insert, =2 Update\/save, =3 Delete\r\n @action TINYINT \r\n ,@InvoiceNo VARCHAR(20) = NULL\r\n ,@InvoiceDate DATE = NULL\r\n ,@CustomerID VARCHAR(20) = NULL\r\n ,@CustomerName VARCHAR(100) = NULL\r\n ,@CustomerAddr1 VARCHAR(100) = NULL\r\n ,@CustomerAddr2 VARCHAR(100) = NULL\r\n ,@CustomerZipcode VARCHAR(10) = NULL\r\n)\r\nAS BEGIN;\r\n SET NOCOUNT ON;\r\n\r\n\/*******************************************************************************\r\n *** Declarations Section *** \r\n \r\n This includes some local control variables (@Error_Code and @Error_Message\r\n will always be among them) and a table variable that is identical in format\r\n to the underlying table we are maintaining, but without the PRIMARY KEY, \r\n FOREIGN KEYS (or other constraints), which allows NULL values in any column.\r\n \r\n Note that for a table that has lots of columns, it is a good practice to use \r\n SQL Server Management Studio (SSMS) to script the CREATE TABLE statement and\r\n then edit that result down for the table variable to ensure that column data\r\n types are consistent.\r\n*******************************************************************************\/\r\n DECLARE @Error_Code INT = 0 \r\n ,@Error_Message VARCHAR(MAX)\r\n ,@NextInvoiceNo BIGINT\r\n ,@InvoicePrefix VARCHAR(5);\r\n \r\n DECLARE @Invoice TABLE\r\n (\r\n InvoiceNo VARCHAR(20) NULL\r\n ,InvoiceDate DATE NULL\r\n ,CustomerID VARCHAR(20) NULL\r\n ,CustomerName VARCHAR(100) NULL\r\n ,CustomerAddr1 VARCHAR(100) NULL\r\n ,CustomerAddr2 VARCHAR(100) NULL\r\n ,CustomerZipcode VARCHAR(10) NULL\r\n );\r\n \r\n\/*******************************************************************************\r\n *** Parameters Cleansing Section ***\r\n \r\n Convert blank values to NULL, transform any other values to something \r\n appropriate for the @action, etc. \r\n*******************************************************************************\/\r\n\r\n -- Cleanse\/transform the input parameters\r\n SELECT @action = CASE \r\n WHEN @action IN (0, 1, 2, 3) THEN @action \r\n ELSE -1 \r\n END\r\n -- Convert blanks passed to parameters to NULL \r\n -- in case the developers didn't\r\n ,@InvoiceNo = NULLIF(@InvoiceNo, '')\r\n ,@CustomerID = NULLIF(@CustomerID, '')\r\n ,@CustomerName = NULLIF(@CustomerName, '')\r\n ,@CustomerAddr1 = NULLIF(@CustomerAddr1, '')\r\n ,@CustomerAddr2 = NULLIF(@CustomerAddr2, '')\r\n ,@CustomerZipcode = NULLIF(@CustomerZipcode, '');\r\n \r\n\/*******************************************************************************\r\n *** Parameters Auditing Section ***\r\n \r\n Parameters auditing generally occur in these three parts in this sequence:\r\n \r\n 1. Control audits: These audits are performed to make sure that appropriate\r\n parameters are passed to the SP on the specific actions. Generally in\r\n a production application you would not see these messages returned to\r\n the form, because they represent cases where the developer did not \r\n call the SP correctly (so should be corrected during forms testing).\r\n 2. Simple data audits: Includes things like making sure required values\r\n (NOT NULL in the database table) are provided, consistency checks\r\n between data elements, i.e., anything that does not require a \r\n validation against the database. Another example is auditing a data\r\n element against the CHECK constraint for the underlying column.\r\n 3. Complex data audits: These are any audits requiring validating\r\n that a specified parameter is valid based on existing rows in other \r\n table. For example, making sure FOREIGN KEY constraints are met.\r\n \r\n It may be necessary in some cases to perform audits of typs 3 mixed in \r\n with audits of type 2 when it is necessary to do several validations in a \r\n specific sequence. That is permissible, but generally when possible the \r\n sequence should be adhered to.\r\n \r\n Always check that @Error_Code still equals zero before you perform the\r\n next audit. This is especially important for complex audits to avoid\r\n multiple retrievals from the database (keeps the performance up).\r\n \r\n When commenting any particular audit, specify what the data should be, i.e.,\r\n the opposite information from what the code audit performs (what the data\r\n should not be). This adds clarity.\r\n*******************************************************************************\/\r\n\r\n\/*******************************************************************************\r\n *** Perform Type 1 (Control) audits\r\n*******************************************************************************\/\r\n\r\n -- After cleansing, @action should be 0,1,2,3\r\n IF @Error_Code = 0 AND @action NOT IN (0, 1, 2, 3) \r\n SELECT @Error_Code = 1;\r\n\r\n -- @InvoiceNo required on query or delete\r\n IF @Error_Code = 0 AND @action IN (0, 3) AND @InvoiceNo IS NULL\r\n SELECT @Error_Code = 2;\r\n \r\n -- @InvoiceNo not allowed on insert (generated on save)\r\n IF @Error_Code = 0 AND @action = 1 AND @InvoiceNo IS NOT NULL\r\n SELECT @Error_Code = 3;\r\n\r\n -- @CustomerID is required on insert\r\n IF @Error_Code = 0 AND @action = 1 AND @CustomerID IS NULL\r\n SELECT @Error_Code = 4;\r\n \r\n\/*******************************************************************************\r\n *** Perform Type 2 (Simple Data) audits\r\n*******************************************************************************\/\r\n\r\n -- Validate required (NOT NULL) fields on update\/save \r\n IF @Error_Code = 0 AND @action = 2 AND @InvoiceDate IS NULL \r\n SELECT @Error_Code = 5;\r\n ELSE IF @Error_Code = 0 AND @action = 2 AND @CustomerID IS NULL \r\n SELECT @Error_Code = 6;\r\n ELSE IF @Error_Code = 0 AND @action = 2 AND @CustomerName IS NULL \r\n SELECT @Error_Code = 7;\r\n ELSE IF @Error_Code = 0 AND @action = 2 AND @CustomerAddr1 IS NULL \r\n SELECT @Error_Code = 8;\r\n ELSE IF @Error_Code = 0 AND @action = 2 AND @CustomerZipcode IS NULL \r\n SELECT @Error_Code = 9;\r\n\r\n\/*******************************************************************************\r\n *** Perform Type 3 (Complex Data) audits\r\n*******************************************************************************\/\r\n\r\n -- Check that the invoice exists\r\n IF @Error_Code = 0 AND @InvoiceNo IS NOT NULL AND NOT EXISTS\r\n (\r\n SELECT 1\r\n FROM dbo.Invoices\r\n WHERE InvoiceNo = @InvoiceNo\r\n ) SELECT @Error_Code = 10;\r\n\r\n -- Confirm foreign key constraints before saving \r\n IF @Error_Code = 0 AND @CustomerID IS NOT NULL AND NOT EXISTS\r\n (\r\n SELECT 1\r\n FROM dbo.Customers\r\n WHERE CustomerID = @CustomerID\r\n ) SELECT @Error_Code = 11;\r\n\r\n\/*******************************************************************************\r\n *** Actions Section ***\r\n \r\n This section processes each possible action the user can specify by a \r\n button push on the form.\r\n \r\n Only process actions if @Error_Code = 0.\r\n \r\n Note that the Insert @action does not really put any data into the \r\n underlying table. All it does is populate default values for each column\r\n for display on the form.\r\n \r\n The invoice number is not generated until a save action occurs.\r\n*******************************************************************************\/\r\n\r\n -- Perform the Query @action (=0)\r\n IF @Error_Code = 0 AND @action = 0\r\n -- Store query results in the table variable \r\n INSERT INTO @Invoice\r\n (\r\n InvoiceNo, InvoiceDate, CustomerID, CustomerName\r\n ,CustomerAddr1, CustomerAddr2, CustomerZipcode\r\n )\r\n SELECT InvoiceNo, InvoiceDate, CustomerID, CustomerName\r\n ,CustomerAddr1, CustomerAddr2, CustomerZipcode\r\n FROM dbo.Invoices\r\n WHERE InvoiceNo = @InvoiceNo;\r\n\r\n -- Perform the Insert @action (=1)\r\n IF @Error_Code = 0 AND @action = 1 \r\n -- Store default values in the table variable \r\n INSERT INTO @Invoice\r\n (\r\n InvoiceNo, InvoiceDate, CustomerID, CustomerName\r\n ,CustomerAddr1, CustomerAddr2, CustomerZipcode\r\n )\r\n SELECT NULL, GETDATE(), CustomerID, CustomerName\r\n ,CustomerAddr1, CustomerAddr2, CustomerZipcode\r\n FROM dbo.Customers\r\n WHERE CustomerID = @CustomerID; \r\n \r\n -- Perform the update (and actual Insert) and delete @actions (=2 and 3)\r\n IF @Error_Code = 0 AND @action IN (2, 3) \r\n BEGIN\r\n\/*******************************************************************************\r\n *** Additional Explanation for the Actions Section ***\r\n \r\n Below is the code that actually updates the underlying tables. Always wrap\r\n this in a TRANSACTION with appropriate error handling using TRY\/CATCH.\r\n \r\n MERGE (available in SQL 2008 onwards), is quite useful to minimize the \r\n number of actual SQL statements you need to write to maintain the \r\n underlying table.\r\n \r\n The Counters table (or a SEQUENCE if you're using SQL 2012) generates the\r\n next available invoice number when @InvoiceNo is NULL on save (basically\r\n on an INSERT to the underlying table). The UPDLOCK hint locks that row of\r\n the table until the TRANSACTION completes, to avoid duplicate invoice\r\n numbers.\r\n*******************************************************************************\/\r\n BEGIN TRANSACTION T1;\r\n \r\n BEGIN TRY;\r\n\r\n IF @InvoiceNo IS NULL\r\n -- Generate the next invoice number here\r\n UPDATE dbo.Counters WITH(UPDLOCK)\r\n SET @NextInvoiceNo = CurrentValue = CurrentValue + 1\r\n ,@InvoicePrefix = IDPrefix\r\n WHERE CounterID = 'Invoices';\r\n\r\n -- Cascade delete of the details\r\n IF @action = 3\r\n DELETE FROM dbo.Invoice_Details\r\n WHERE InvoiceNo = @InvoiceNo;\r\n \r\n -- And now perform the save\/delete (t=target table, s=source table)\r\n MERGE dbo.Invoices t\r\n USING\r\n (\r\n SELECT InvoiceNo = ISNULL(@InvoiceNo, @InvoicePrefix + \r\n CAST(YEAR(@InvoiceDate) AS CHAR(4))+ \r\n CAST(@NextInvoiceNo AS VARCHAR(20)))\r\n ,InvoiceDate = @InvoiceDate\r\n ,CustomerID = @CustomerID\r\n ,CustomerName = @CustomerName\r\n ,CustomerAddr1 = @CustomerAddr1\r\n ,CustomerAddr2 = @CustomerAddr2\r\n ,CustomerZipcode = @CustomerZipcode\r\n ) s\r\n ON s.InvoiceNo = t.InvoiceNo\r\n WHEN MATCHED AND @action = 3\r\n THEN DELETE\r\n WHEN MATCHED AND @action = 2\r\n THEN UPDATE\r\n SET InvoiceNo = s.InvoiceNo\r\n ,InvoiceDate = s.InvoiceDate\r\n ,CustomerID = s.CustomerID\r\n ,CustomerName = s.CustomerName\r\n ,CustomerAddr1 = s.CustomerAddr1\r\n ,CustomerAddr2 = s.CustomerAddr2\r\n ,CustomerZipcode = s.CustomerZipcode\r\n WHEN NOT MATCHED\r\n THEN INSERT\r\n (\r\n InvoiceNo, InvoiceDate, CustomerID, CustomerName\r\n ,CustomerAddr1, CustomerAddr2, CustomerZipcode\r\n )\r\n VALUES\r\n (\r\n s.InvoiceNo, s.InvoiceDate, s.CustomerID, s.CustomerName\r\n ,s.CustomerAddr1, s.CustomerAddr2, s.CustomerZipcode\r\n )\r\n -- Use OUTPUT to capture the updated results\r\n -- Note that on DELETE, all of these values will be NULL\r\n OUTPUT INSERTED.InvoiceNo, INSERTED.InvoiceDate, INSERTED.CustomerID\r\n ,INSERTED.CustomerName, INSERTED.CustomerAddr1\r\n ,INSERTED.CustomerAddr2, INSERTED.CustomerZipcode\r\n INTO @Invoice;\r\n \r\n END TRY\r\n \r\n BEGIN CATCH;\r\n -- Some basic error handling\r\n SELECT @Error_Code = ERROR_NUMBER()\r\n ,@Error_Message = ERROR_MESSAGE();\r\n PRINT 'Merge to Invoices table';\r\n\r\n -- Useful information for debugging \r\n PRINT 'ERROR_NUMBER() '+ CAST(ERROR_NUMBER() AS VARCHAR(10));\r\n PRINT 'ERROR_SEVERITY() '+ CAST(ERROR_SEVERITY() AS VARCHAR(10));\r\n PRINT 'ERROR_STATE() '+ CAST(ERROR_STATE() AS VARCHAR(10));\r\n PRINT 'ERROR_PROCEDURE() '+CAST(ERROR_PROCEDURE() AS VARCHAR(8000));\r\n PRINT 'ERROR_LINE() '+ CAST(ERROR_LINE() AS VARCHAR(100));\r\n PRINT 'ERROR_MESSAGE() '+ CAST(ERROR_MESSAGE() AS VARCHAR(8000));\r\n PRINT 'XACT_STATE() '+ CAST(XACT_STATE() AS VARCHAR(5));\r\n END CATCH;\r\n \r\n IF @Error_Code <> 0 OR XACT_STATE() = -1 ROLLBACK TRANSACTION T1;\r\n ELSE IF @Error_Code = 0 AND XACT_STATE() = 1 COMMIT TRANSACTION T1;\r\n END;\r\n\r\n\/*******************************************************************************\r\n *** Returned Tables Section ***\r\n \r\n The table names you can reference in the front-end code.\r\n \r\n In this case:\r\n \r\n 1. ReturnErrors - an error message suitable for display on the form\r\n 2. InvoiceHeader - All columns needed to refresh all form fields in the\r\n invoice header.\r\n 3. InvoiceDetails - All columns needed to refresh all fields in the grid\r\n showing the invoice details.\r\n*******************************************************************************\/\r\n\r\n -- This SP returns 3 tables to the front end:\r\n -- ReturnErrors\r\n -- InvoiceHeader\r\n -- InvoiceHeader\r\n\r\n -- ReturnErrors: whether the action was successful\r\n WITH ReturnErrors (ErrorCode, ErrorMessage) AS\r\n (\r\n SELECT 0, 'Requested Operation Successful'\r\n -- Audit failures \r\n UNION ALL SELECT 1, 'Invalid action specified'\r\n UNION ALL SELECT 2, 'Invoice number is required for this action'\r\n UNION ALL SELECT 3, 'Invoice number is not allowed for this action'\r\n UNION ALL SELECT 4, 'Customer ID is required for this action'\r\n UNION ALL SELECT 5, 'Not saved - Invoice date is required'\r\n UNION ALL SELECT 6, 'Not saved - Customer ID is required'\r\n UNION ALL SELECT 7, 'Not saved - Customer name is required'\r\n UNION ALL SELECT 8, 'Not saved - Customer address is required'\r\n UNION ALL SELECT 9, 'Not saved - Customer zipcode is required'\r\n UNION ALL SELECT 10, 'Specified invoice number does not exist'\r\n UNION ALL SELECT 11, 'Specified customer ID does not exist'\r\n UNION ALL \r\n -- For this case @Error_Code contains a SQL error (e.g., deadlock)\r\n SELECT @Error_Code, @Error_Message\r\n WHERE @Error_Code NOT BETWEEN 0 AND 11 \r\n -- End of the BETWEEN range must correspond to the number of audit \r\n -- failure error messages above \r\n )\r\n SELECT ErrorCode, RequestedAction=@action, ErrorMessage\r\n FROM ReturnErrors ReturnErrors\r\n WHERE @Error_Code = ErrorCode;\r\n\r\n -- InvoiceHeader: Return the invoice header or\r\n -- nothing if there's an error or when action is to delete\r\n SELECT InvoiceNo, InvoiceDate, CustomerID, CustomerName\r\n ,CustomerAddr1, CustomerAddr2, CustomerZipcode\r\n FROM @Invoice InvoiceHeader\r\n WHERE @Error_Code = 0 AND @action <> 3;\r\n\r\n -- InvoiceDetails Return the invoice details \r\n -- InvoiceLineNo is for display in the grid\r\n SELECT InvoiceNo, LineItem, InvoiceLineNo, ItemNo, ItemDescription\r\n ,Quantity, Price, ExtendedPrice\r\n FROM\r\n (\r\n SELECT InvoiceNo, LineItem\r\n ,InvoiceLineNo = ROW_NUMBER() OVER (ORDER BY LineItem)\r\n ,a.ItemNo\r\n ,ItemDescription = b.ItemDesc\r\n ,Quantity, Price\r\n ,ExtendedPrice\r\n FROM dbo.Invoice_Details a\r\n JOIN dbo.Items b\r\n ON a.ItemNo = b.ItemNo\r\n WHERE InvoiceNo = ISNULL(@InvoiceNo, @InvoicePrefix + \r\n CAST(YEAR(@InvoiceDate) AS CHAR(4)) + \r\n CAST(@NextInvoiceNo AS VARCHAR(20))) \r\n ) InvoiceDetails\r\n WHERE @Error_Code = 0; \r\n\r\nEND;\r\n<\/pre>\n
Let’s now describe the relevant components of our SP template:<\/p>\n
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).<\/p>\n
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.<\/p>\n
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.<\/p>\n
4. The auditing section, which consists of three parts:<\/p>\n
\na. A section that makes sure the SP was called correctly. These errors should never appear when testing the form (where @Error_Code IN (1, 2, 3, 4)).<\/p>\n
b. A section that performs any audits on the data passed in, which should be done in the form instead. These are audits that do not require a check against a database table. For example, making sure that required data is supplied, data range checks (such as what might be done by a CHECK constraint on a table column), etc. These are where @Error_Code IN (5, 6, 7, 8, 9).<\/span><\/p>\n
c. A final section that performs any audits against other tables in the database. This normally consists of audits to enforce FOREIGN KEY constraints. Often, other more complex business rules may also be enforced in this section. For example, suppose you had different types of invoices for different customer types. You may have an audit here that determines first the customer’s type and then that the invoice type is valid for that customer. These are where @Error_Code IN (10,11). <\/p>\n<\/p><\/div>\n
5. Then there is the “actions” section, also broken into three parts:<\/p>\n
\na. For @action=0 (query), we simply retrieve the row from the underlying table into our table variable.<\/p>\n
b. For @action=1 (insert), we define all the defaults we’ll return to the form for display (we can call this a stub header).<\/p>\n
c. For @action IN (2,3) (save and delete) we have to define the most complex part of the processing:<\/p>\n
\ni. Begin a transaction. The next steps may, depending on the action, modify multiple tables, so you’d want all of those to complete successfully. Or if one of the steps fails, do a ROLLBACK to ensure that none of them completed.<\/p>\n
ii. Start a TRY\/CATCH block, to deal with any errors during the actual data processing (e.g., a deadlock).<\/p>\n
iii. Allocate the next invoice number from the Counters table. In SQL 2012 or later, you may prefer to use a SEQUENCE for this. We use the UPDLOCK hint on the table to make sure no one else can allocate a sequence number until our transaction completes.<\/p>\n
iv. Delete the details when the action is ‘delete<\/i>‘.<\/p>\n
v. Do a MERGE that separately handles the update\/delete actions (based on @action) and does an insert of the newly created invoice number on insert.<\/p>\n
vi. Note how the MERGE dumps the results using an OUTPUT clause into our table variable, including a row of NULL values in the event a delete occurred (we’ll remove that later).<\/p>\n
vii. In the CATCH block, we have some generic error handling that is useful when we run the SP in SQL Server Management Studio and need to know more about any errors it generates (displayed in the Messages pane).<\/p>\n
viii. Then we COMMIT or ROLLBACK the transaction, depending if we had an error or not.<\/p>\n<\/p><\/div>\n<\/p><\/div>\n
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. <\/p>\n
\na. ReturnErrors<\/b> containing an error code and error message in one row, the latter of which can be displayed directly on our form.<\/p>\n
b. InvoiceHeader<\/b> containing one row to use to refresh the form’s fields for the header. Note that if the ErrorCode<\/b> column of the ReturnErrors <\/b>table is not zero (or if this was a delete), no row is returned.<\/p>\n
c. InvoiceDetails<\/b> contains all the details for this invoice. Again, no rows are returned for the case of the ErrorCode<\/b> being non-zero or for a delete action. Note that two line numbers are returned: LineItem<\/b> (the table-specific line item for an invoice detail record) and InvoiceLineNo<\/b> (a sequential row number used for display purposes only in the form’s grid).<\/p>\n<\/p><\/div>\n
Notice also how we perform the audits only until one of them fails (IF @Error_Code = 0 AND …)<\/b> 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.<\/p>\n
We’ll show some results of testing for this SP in the next section.<\/p>\n
Some Test Data and Initial Testing Scripts for the Header SP<\/h2>\n
Thanks to the wonders of deferred name resolution<\/a>, 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.<\/p>\n
CREATE TABLE dbo.Counters\r\n(\r\n CounterID VARCHAR(10) NOT NULL\r\n ,CurrentValue BIGINT NOT NULL\r\n ,IDPrefix VARCHAR(5) NOT NULL\r\n ,PRIMARY KEY (CounterID)\r\n);\r\n\r\n-- Seed the Counters table (defines the current invoice number) \r\nINSERT INTO dbo.Counters (CounterID, CurrentValue, IDPrefix)\r\nSELECT 'Invoices', 1000001, 'IN';\r\n\r\nCREATE TABLE dbo.Customers\r\n(\r\n CustomerID VARCHAR(20) NOT NULL\r\n ,CustomerName VARCHAR(100) NOT NULL\r\n ,CustomerAddr1 VARCHAR(100) NOT NULL\r\n ,CustomerAddr2 VARCHAR(100) NULL\r\n ,CustomerZipcode VARCHAR(10) NOT NULL\r\n PRIMARY KEY (CustomerID)\r\n);\r\n\r\n-- Seed the Customers table \r\nINSERT INTO dbo.Customers \r\n(\r\n CustomerID, CustomerName, CustomerAddr1, CustomerAddr2, CustomerZipcode\r\n)\r\nSELECT '20001', 'ACME MINERALS', '35 WESTCHESTER ST', 'CAIRNS, AU', '4810'\r\nUNION ALL SELECT '20002', 'ACE CHEMICALS', '22 BROAD AVE', 'CAIRNS, AU', '4810';\r\n\r\nCREATE TABLE dbo.Items\r\n(\r\n ItemNo VARCHAR(20) NOT NULL\r\n ,ItemDesc VARCHAR(100) NOT NULL\r\n ,ItemPrice MONEY NOT NULL\r\n PRIMARY KEY (ItemNo)\r\n);\r\n\r\n-- Seed the Items table \r\nINSERT INTO dbo.Items (ItemNo, ItemDesc, ItemPrice)\r\nSELECT '1000055', 'ORE SORTER', 233.22\r\nUNION ALL SELECT '1000056', 'CONVEYER BELT', 189.33\r\nUNION ALL SELECT '1000057', 'LATHE', 1032.22\r\nUNION ALL SELECT '1000058', 'MURIATIC ACID', 31.11\r\nUNION ALL SELECT '1000059', 'FULMINATE OF MERCURY', 212.23;\r\n\r\nCREATE TABLE dbo.Invoices\r\n(\r\n InvoiceNo VARCHAR(20) NOT NULL\r\n ,InvoiceDate DATE NOT NULL\r\n ,CustomerID VARCHAR(20) NOT NULL\r\n ,CustomerName VARCHAR(100) NOT NULL\r\n ,CustomerAddr1 VARCHAR(100) NOT NULL\r\n ,CustomerAddr2 VARCHAR(100) NULL\r\n ,CustomerZipcode VARCHAR(10) NOT NULL\r\n ,PRIMARY KEY (InvoiceNo)\r\n ,CONSTRAINT inv_fk1 FOREIGN KEY (CustomerID) REFERENCES dbo.Customers (CustomerID)\r\n);\r\n\r\n-- Seed the Invoices table\r\nINSERT INTO dbo.Invoices \r\n(\r\n InvoiceNo, InvoiceDate, CustomerID, CustomerName\r\n ,CustomerAddr1, CustomerAddr2, CustomerZipcode\r\n)\r\nSELECT 'IN20141000001', '2014-11-03', '20001', 'ACME MINERALS', '35 WESTCHESTER ST'\r\n,'CAIRNS, AU', '4810';\r\n\r\nCREATE TABLE dbo.Invoice_Details\r\n(\r\n InvoiceNo VARCHAR(20) NOT NULL\r\n ,LineItem BIGINT IDENTITY\r\n ,ItemNo VARCHAR(20) NOT NULL\r\n ,Quantity INT NOT NULL\r\n ,Price MONEY NOT NULL\r\n ,ExtendedPrice AS (Quantity * Price)\r\n ,PRIMARY KEY (InvoiceNo, LineItem)\r\n ,CONSTRAINT invd_fk1 FOREIGN KEY (InvoiceNo) REFERENCES dbo.Invoices (InvoiceNo)\r\n ,CONSTRAINT invd_fk2 FOREIGN KEY (ItemNo) REFERENCES dbo.Items (ItemNo)\r\n);\r\n\r\n-- Seed the Invoice Details table\r\nINSERT INTO dbo.Invoice_Details (InvoiceNo, ItemNo, Quantity, Price)\r\nSELECT 'IN20141000001', '1000055', 12, 233.22\r\nUNION ALL SELECT 'IN20141000001', '1000056', 3, 189.33;\r\n\r\n\/*\r\nGO\r\nDROP PROCEDURE dbo.Maintain_Invoice_Header;\r\nDROP PROCEDURE dbo.Maintain_Invoice_Details;\r\nDROP TABLE dbo.Invoice_Details;\r\nDROP TABLE dbo.Invoices;\r\nDROP TABLE dbo.Items;\r\nDROP TABLE dbo.Customers;\r\nDROP TABLE dbo.Counters;\r\n*\/\r\n<\/pre>\n
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.<\/p>\n
The DROPs are provided as a ‘teardown’ code to help you clean up your sandbox once all is said and done.<\/p>\n
Let’s now look at some test scripts and their results.<\/p>\n
-- Query an invoice that doesn't exist\r\nEXEC dbo.Maintain_Invoice_Header\r\n @action = 0\r\n ,@InvoiceNo = 'IN20141000002';\r\n<\/pre>\n
![\"2097-clip_image003.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2097-clip_image003.png\")
<\/p>\n-- Query an existing invoice\r\nEXEC dbo.Maintain_Invoice_Header\r\n @action = 0\r\n ,@InvoiceNo = 'IN20141000001';\r\n<\/pre>\n
![\"2097-clip_image005.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2097-clip_image005.jpg\")
<\/p>\n-- Insert mode: specify a Customer ID that doesn't exist\r\nEXEC dbo.Maintain_Invoice_Header\r\n @action = 1\r\n ,@CustomerID = '20003';\r\n<\/pre>\n
![\"2097-clip_image006.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2097-clip_image006.png\")
<\/p>\n-- Insert mode: specify a Customer ID that does exist\r\nEXEC dbo.Maintain_Invoice_Header\r\n @action = 1\r\n ,@CustomerID = '20002';\r\n<\/pre>\n
![\"2097-clip_image007.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2097-clip_image007.png\")
<\/p>\n-- Update the invoice date for an existing invoice\r\nEXEC dbo.Maintain_Invoice_Header\r\n @action = 2\r\n ,@InvoiceNo = 'IN20141000001'\r\n ,@InvoiceDate = '2014-11-04'\r\n ,@CustomerID = '20002'\r\n ,@CustomerName = 'ACE CHEMICALS'\r\n ,@CustomerAddr1 = '22 BROAD AVE'\r\n ,@CustomerAddr2 = 'CAIRNS, AU'\r\n ,@CustomerZipcode = '4810';\r\n<\/pre>\n
![\"2097-clip_image009.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2097-clip_image009.jpg\")
<\/p>\n-- Save a new invoice date for the specified customer\r\nEXEC dbo.Maintain_Invoice_Header\r\n @action = 2\r\n ,@InvoiceNo = NULL\r\n ,@InvoiceDate = '2014-11-03'\r\n ,@CustomerID = '20001'\r\n ,@CustomerName = 'ACME MINERALS'\r\n ,@CustomerAddr1 = '35 WESTCHESTER ST'\r\n ,@CustomerAddr2 = 'CAIRNS, AU'\r\n ,@CustomerZipcode = '4810';\r\n<\/pre>\n
![\"2097-clip_image011.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2097-clip_image011.jpg\")
<\/p>\n-- Delete an existing invoice (and its details)\r\nEXEC dbo.Maintain_Invoice_Header\r\n @action = 3\r\n ,@InvoiceNo = 'IN20141000001'\r\n ,@InvoiceDate = NULL\r\n ,@CustomerID = NULL\r\n ,@CustomerName = NULL\r\n ,@CustomerAddr1 = NULL\r\n ,@CustomerAddr2 = NULL\r\n ,@CustomerZipcode = NULL;\r\n<\/pre>\n
![\"2097-clip_image012.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2097-clip_image012.png\")
<\/p>\nWe’ll leave it to our interested readers to verify that all audit rules trigger the correct error messages when an audit fails.<\/p>\n
An Invoice Details SP Template and some Test Scripts<\/h2>\n
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.<\/p>\n
CREATE PROCEDURE dbo.Maintain_Invoice_Details\r\n-- =============================================================================\r\n-- Author: Dwain.C\r\n-- Create date: 15-Nov-2014\r\n-- Description: This is a template for an SP that maintains the details of an \r\n-- invoice document. \/* Comment blocks *\/ are explanations of the \r\n-- sections of the template and should be removed in a customized \r\n-- version of the template.\r\n-- =============================================================================\r\n(\r\n\/*******************************************************************************\r\n ** Parameters Section **\r\n \r\n Which includes:\r\n - An @action variable that corresponds to form actions (buttons), along with\r\n any other parameters such as the user that is performing the action, \r\n which could be used to maintain a record of the person performing the \r\n change.\r\n - The data values (form fields) which will be updated on that action. Note \r\n that this includes @InvoiceNo, which is required on all actions.\r\n \r\n After the @action variable, default values for all parameters are defaulted \r\n to NULL, because the SP will validate (audit) that appropriate values are \r\n provided on each specific action.\r\n \r\n It is a best practice to call this SP using specified parameter names rather \r\n than assuming that the positional placement won't change.\r\n \r\n Note that data parameters (including @InvoiceNo) should be identical to the \r\n data types for the underlying table's columns, so that when you publish the \r\n SP's call signature the developers know how that data is stored in the \r\n front-end code.\r\n*******************************************************************************\/\r\n @action TINYINT -- =2 Insert\/update, =3 Delete\r\n ,@InvoiceNo VARCHAR(20) \r\n -- If @LineItem IS NULL then insert on @action=2 \r\n ,@LineItem BIGINT = NULL \r\n ,@ItemNo VARCHAR(20) = NULL\r\n ,@Quantity INT = NULL\r\n -- If @Price IS NULL then get the price from the Items table\r\n ,@Price MONEY = NULL \r\n)\r\nAS BEGIN;\r\n SET NOCOUNT ON;\r\n\r\n\/*******************************************************************************\r\n *** Declarations Section *** \r\n \r\n This includes some local control variables (@Error_Code and @Error_Message\r\n will always be among them).\r\n\r\n*******************************************************************************\/\r\n\r\n DECLARE @Error_Code INT = 0 \r\n ,@Error_Message VARCHAR(MAX);\r\n \r\n\/*******************************************************************************\r\n *** Parameters Cleansing Section ***\r\n \r\n Convert blank values to NULL, transform any other values to something \r\n appropriate for the @action, etc. \r\n*******************************************************************************\/\r\n\r\n -- Cleanse\/transform the input parameters\r\n SELECT @action = CASE \r\n WHEN @action IN (2, 3) THEN @action \r\n ELSE -1 \r\n END\r\n ,@InvoiceNo = NULLIF(@InvoiceNo, '')\r\n ,@ItemNo = NULLIF(@ItemNo, '');\r\n \r\n\/*******************************************************************************\r\n *** Parameters Auditing Section ***\r\n \r\n Parameters auditing generally occur in these three parts in this sequence:\r\n \r\n 1. Control audits: These audits are performed to make sure that appropriate\r\n parameters are passed to the SP on the specific actions. Generally in\r\n a production application you would not see these messages returned to\r\n the form, because they represent cases where the developer did not \r\n call the SP correctly (so should be corrected during forms testing).\r\n 2. Simple data audits: Includes things like making sure required values\r\n (NOT NULL in the database table) are provided, consistency checks\r\n between data elements, i.e., anything that does not require a \r\n validation against the database. Another example is auditing a data\r\n element against the CHECK constraint for the underlying column.\r\n 3. Complex data audits: These are any audits requiring validating\r\n that a specified parameter is valid based on existing rows in other \r\n table. For example, making sure FOREIGN KEY constraints are met.\r\n \r\n It may be necessary in some cases to perform audits of typs 3 mixed in \r\n with audits of type 2 when it is necessary to do several validations in a \r\n specific sequence. That is permissible, but generally when possible the \r\n sequence should be adhered to.\r\n \r\n Always check that @Error_Code still equals zero before you perform the\r\n next audit. This is especially important for complex audits to avoid\r\n multiple retrievals from the database (keeps the performance up).\r\n \r\n When commenting any particular audit, specify what the data should be, i.e.,\r\n the opposite information from what the code audit performs (what the data\r\n should not be). This adds clarity.\r\n*******************************************************************************\/\r\n\r\n\/*******************************************************************************\r\n *** Perform Type 1 (Control) audits\r\n*******************************************************************************\/\r\n\r\n -- After cleansing, @action should be 2,3\r\n IF @Error_Code = 0 AND @action NOT IN (2, 3) \r\n SELECT @Error_Code = 1;\r\n\r\n -- @InvoiceNo required on all actions\r\n IF @Error_Code = 0 AND @InvoiceNo IS NULL\r\n SELECT @Error_Code = 2;\r\n \r\n -- @LineItem is required on delete, optional on Insert\/Update\r\n IF @Error_Code = 0 AND @action = 3 AND @LineItem IS NULL\r\n SELECT @Error_Code = 3;\r\n \r\n\/*******************************************************************************\r\n *** Perform Type 2 (Simple Data) audits\r\n*******************************************************************************\/\r\n\r\n -- Validate required (NOT NULL) fields on update\/save \r\n IF @Error_Code = 0 AND @action = 2 AND @ItemNo IS NULL \r\n SELECT @Error_Code = 4;\r\n ELSE IF @Error_Code = 0 AND @action = 2 AND @Quantity IS NULL \r\n SELECT @Error_Code = 5;\r\n -- And maybe do some other data range or validity checks\r\n ELSE IF @Error_Code = 0 AND @action = 2 AND @Quantity <=0 \r\n SELECT @Error_Code = 6;\r\n\r\n\/*******************************************************************************\r\n *** Perform Type 3 (Complex Data) audits\r\n*******************************************************************************\/\r\n\r\n -- Check that the invoice exists\r\n IF @Error_Code = 0 AND @InvoiceNo IS NOT NULL AND NOT EXISTS\r\n (\r\n SELECT 1\r\n FROM dbo.Invoices\r\n WHERE InvoiceNo = @InvoiceNo\r\n ) SELECT @Error_Code = 7;\r\n\r\n -- Check that the line number (if specified) exists\r\n IF @Error_Code = 0 AND @LineItem IS NOT NULL AND NOT EXISTS\r\n (\r\n SELECT 1\r\n FROM dbo.Invoice_Details\r\n WHERE InvoiceNo = @InvoiceNo AND LineItem = @LineItem\r\n ) SELECT @Error_Code = 8;\r\n\r\n -- Confirm foreign key constraints before proceeding \r\n IF @Error_Code = 0 AND @action = 2 AND @ItemNo IS NOT NULL AND NOT EXISTS\r\n (\r\n SELECT 1\r\n FROM dbo.Items\r\n WHERE ItemNo = @ItemNo\r\n ) SELECT @Error_Code = 9;\r\n\r\n\/*******************************************************************************\r\n *** Actions Section ***\r\n \r\n This section processes each possible action the user can specify by a \r\n button push on the form.\r\n \r\n Only process actions if @Error_Code = 0.\r\n*******************************************************************************\/\r\n\r\n -- If no errors, then process each action\r\n IF @Error_Code = 0 \r\n BEGIN\r\n BEGIN TRANSACTION T1;\r\n \r\n BEGIN TRY;\r\n\r\n -- And now perform the insert\/update\/delete\r\n MERGE dbo.Invoice_Details t\r\n USING\r\n (\r\n SELECT InvoiceNo = @InvoiceNo\r\n ,LineItem = @LineItem\r\n ,ItemNo = @ItemNo\r\n ,Quantity = @Quantity\r\n ,Price = ISNULL(@Price, ItemPrice)\r\n -- Ensure at least one row returned to support delete\r\n FROM (SELECT 1) a (n)\r\n LEFT JOIN dbo.Items b ON ItemNo = @ItemNo\r\n ) s\r\n ON s.InvoiceNo = t.InvoiceNo AND s.LineItem = t.LineItem\r\n WHEN MATCHED AND @action = 3\r\n THEN DELETE\r\n WHEN MATCHED AND @action = 2\r\n THEN UPDATE\r\n SET InvoiceNo = s.InvoiceNo\r\n ,ItemNo = s.ItemNo\r\n ,Quantity = s.Quantity\r\n ,Price = s.Price\r\n WHEN NOT MATCHED\r\n THEN INSERT\r\n (\r\n InvoiceNo, ItemNo, Quantity, Price\r\n )\r\n VALUES\r\n (\r\n s.InvoiceNo, s.ItemNo, s.Quantity, s.Price\r\n );\r\n \r\n END TRY\r\n \r\n BEGIN CATCH;\r\n -- Some basic error handling\r\n SELECT @Error_Code = ERROR_NUMBER(), @Error_Message = ERROR_MESSAGE();\r\n PRINT 'Merge to Invoice details table';\r\n \r\n -- Useful information for debugging \r\n PRINT 'ERROR_NUMBER()' + CAST(ERROR_NUMBER() AS VARCHAR(10));\r\n PRINT 'ERROR_SEVERITY()' + CAST(ERROR_SEVERITY() AS VARCHAR(10));\r\n PRINT 'ERROR_STATE()' + CAST(ERROR_STATE() AS VARCHAR(10));\r\n PRINT 'ERROR_PROCEDURE() ' + CAST(ERROR_PROCEDURE() AS VARCHAR(8000));\r\n PRINT 'ERROR_LINE() ' + CAST(ERROR_LINE() AS VARCHAR(100));\r\n PRINT 'ERROR_MESSAGE() ' + CAST(ERROR_MESSAGE() AS VARCHAR(8000));\r\n PRINT 'XACT_STATE() ' + CAST(XACT_STATE() AS VARCHAR(5));\r\n END CATCH;\r\n \r\n IF @Error_Code <> 0 OR XACT_STATE() = -1 ROLLBACK TRANSACTION T1;\r\n ELSE IF @Error_Code = 0 AND XACT_STATE() = 1 COMMIT TRANSACTION T1;\r\n END;\r\n\r\n\/*******************************************************************************\r\n *** Returned Tables Section ***\r\n \r\n The table names you can reference in the front-end code.\r\n \r\n In this case:\r\n \r\n 1. ReturnErrors - an error message suitable for display on the form\r\n*******************************************************************************\/\r\n\r\n -- SP Returns 1 table to the front end:\r\n -- ReturnErrors\r\n\r\n -- ReturnErrors: whether the action was successful\r\n WITH ReturnErrors (ErrorCode, ErrorMessage) AS\r\n (\r\n SELECT 0, 'Requested Operation Successful' \r\n -- Audit failures \r\n UNION ALL SELECT 1, 'Invalid action specified'\r\n UNION ALL SELECT 2, 'Invoice number is required for this action'\r\n UNION ALL SELECT 3, 'Line item is required for this action'\r\n UNION ALL SELECT 4, 'Not saved - Item number is required'\r\n UNION ALL SELECT 5, 'Not saved - Quantity is required'\r\n UNION ALL SELECT 6, 'Not saved - Quantity must be greater than zero'\r\n UNION ALL SELECT 7, 'Specified invoice number does not exist'\r\n UNION ALL SELECT 8, 'Specified line item does not exist'\r\n UNION ALL SELECT 9, 'Specified item number does not exist'\r\n UNION ALL \r\n -- For this case @Error_Code contains a SQL error (e.g., deadlock)\r\n SELECT @Error_Code, @Error_Message\r\n WHERE @Error_Code NOT BETWEEN 0 AND 9 \r\n -- End of the BETWEEN range must correspond to the number of audit \r\n -- failure error messages above \r\n )\r\n SELECT ErrorCode, RequestedAction=@action, ErrorMessage\r\n FROM ReturnErrors ReturnErrors\r\n WHERE @Error_Code = ErrorCode;\r\n\r\nEND;\r\n<\/pre>\n
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.<\/p>\n
The main difference between the details and header template, is that the details template only returns the one table (ReturnErrors<\/b>) 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.<\/p>\n
Now let’s test the available actions for the details (from running the test scripts above we now only have invoice number: IN20141000002 available).<\/p>\n
-- Insert a row to our existing invoice\r\nEXEC dbo.Maintain_Invoice_Details\r\n @action = 2 \r\n ,@InvoiceNo = 'IN20141000002' \r\n ,@LineItem = NULL \r\n ,@ItemNo = '1000058'\r\n ,@Quantity = 2\r\n ,@Price = 32.22 -- override price\r\n\r\nSELECT *\r\nFROM dbo.Invoice_Details;\r\n<\/pre>\n
![\"2097-clip_image013.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2097-clip_image013.png\")
<\/p>\n-- Insert another row to our existing invoice\r\nEXEC dbo.Maintain_Invoice_Details\r\n @action = 2 \r\n ,@InvoiceNo = 'IN20141000002' \r\n ,@LineItem = NULL \r\n ,@ItemNo = '1000059'\r\n ,@Quantity = 4\r\n ,@Price = NULL -- default price \r\n\r\nSELECT *\r\nFROM dbo.Invoice_Details;\r\n<\/pre>\n
![\"2097-clip_image014.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2097-clip_image014.png\")
<\/p>\nNotice 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.<\/p>\n
-- Update price and quantity on LineItem=3\r\nEXEC dbo.Maintain_Invoice_Details\r\n @action = 2 \r\n ,@InvoiceNo = 'IN20141000002' \r\n ,@LineItem = 3 \r\n ,@ItemNo = '1000058'\r\n ,@Quantity = 8\r\n ,@Price = 30.24 \r\n\r\n-- Then query that invoice\r\nEXEC dbo.Maintain_Invoice_Header\r\n @action = 0\r\n ,@InvoiceNo = 'IN20141000002';\r\n<\/pre>\n
![\"2097-clip_image016.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2097-clip_image016.jpg\")
<\/p>\nThe 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.<\/p>\n
-- Delete LineItem=4\r\nEXEC dbo.Maintain_Invoice_Details\r\n @action = 3 \r\n ,@InvoiceNo = 'IN20141000002' \r\n ,@LineItem = 4 \r\n ,@ItemNo = NULL\r\n ,@Quantity = NULL\r\n ,@Price = NULL \r\n\r\n-- Then query that invoice\r\nEXEC dbo.Maintain_Invoice_Header\r\n @action = 0\r\n ,@InvoiceNo = 'IN20141000002';\r\n<\/pre>\n
![\"2097-clip_image018.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2097-clip_image018.jpg\")
<\/p>\nBusiness Logic Left to the Presentation Layer<\/h2>\n
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:<\/p>\n
- \n
- 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).<\/li>\n
- 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.<\/li>\n
- 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.<\/li>\n
- 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.<\/li>\n<\/ul>\n
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<\/b> table’s results and then using these as control flags in the front-end code.<\/p>\n
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.<\/p>\n
Some of the Benefits of the Steps Described Above <\/h2>\n
The table below summarizes some of the additional benefits to using this approach.<\/p>\n