The T-SQL code in Listing 3 creates a system stored procedure named sp_FetchRelatedRows. The routine returns related data similar to the query above, but it returns the data in a separate result set for each table involved. This kind of output might be more useful to a DBA or developer as he or she investigates an issue. All the appropriate rows from all the appropriate tables are presented at once. The rows included for each table are limited to those involved in a parent or child relationship with rows from one or more of the other sets.
The sp_FetchRelatedRows stored procedure accepts seven parameters.
The first parameter (@DBChain) is a list of table names separated by pipes (vertical bars). Each table beyond the first one must be a parent or child of a previous table in the list. Typically, the chain of tables would traverse the hierarchy down from parent to child. When the lowest level table has been specified a table that joins to any table appearing earlier in the list would be specified, starting a new sequence of tables. The new sequence would then traverse up or down the hierarchy as necessary.
The second parameter (@DBWhere) is optional and is used as a WHERE clause with the first table.
The third parameter (@DBAdmin) is optional and it controls how and when joins are established between the tables in the chain specified by @DBChain. The default value is zero (0).
If @DBAdmin is negative then the tables are joined as a unit (like the query above). This option eliminates parent rows that have no child rows (the other options do not).
If @DBAdmin is zero then each table is joined to every preceding table for which a relationship exists. Rows are selected based on the sequence(s) up to that point. This option (or the previous option) is required if any sequence of tables runs parallel to a previous sequence.
If @DBAdmin is positive then each table is joined to only the nearest preceding table for which a relationship exists. Rows are selected based on the sequence(s) up to that point.
The next two parameters (@DBField and @DBValue) are optional and are used to limit the rows selected from the first table, based on a column with an integer value. Typically, a primary key or foreign key column, along with a corresponding value, is used for this purpose.
The next two parameters (@PCField and @PCValue) are optional and are used to limit the rows selected from each table, based on a column with an integer value. If this pair of parameters is used, the indicated column must exist in every table.
You can investigate the data used in the query above with this stored procedure call:
EXECUTE sp_FetchRelatedRows 'Customer|Region|Purchase|PurchaseItem|Product|Vendor|PurchaseItemShipment|Shipment',NULL,0,'CustomerID',2
You can easily include an additional table to investigate with this stored procedure call:
EXECUTE sp_FetchRelatedRows 'Customer|Region|Purchase|PurchaseItem|Product|Vendor|PurchaseItemShipment|Shipment|Payment',NULL,0,'CustomerID',2
The sp_FetchRelatedRows stored procedure is presented here in the context of DBA or development work. However, it's worth mentioning that the output of the routine can be handled very gracefully by ADO.NET. A DataSet object can be populated with all the tables at once using a single method. Furthermore, a generic routine can build all the correct relationships between the DataTable objects in the DataSet. The process creates a mini-database that can be navigated using the GetParentRow and GetChildRows methods. I would not recommend calling sp_FetchRelatedRows from a production application without fully understanding the potential ramifications of using dynamic T-SQL code.
All the stored procedures presented here are made possible, or at least made practical, with a consistent surrogate key architecture. These routines are quite simple, but there are many other much more powerful routines that can use the consistency to great advantage.
Click for the stored procedures to investigate related data
Home: 
