Manage Learn to apply best practices and optimize your operations.

Stored procedure: Investigate related data

Contributor Brian Walker offers this system stored procedure to return related data.

Listing 3

The sp_FetchRelatedRows stored procedure accepts seven parameters.

Click for the stored procedures to investigate related data

The T-SQL code in 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 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.

A surrogate key architecture for powerful database operations

 Home: Introduction
 Part 1: Why use surrogate keys
 Part 2: A surrogate key architecture
 Part 3: Stored procedures: Create and delete constraints and indexes
 Part 4: Stored procedure: Examine children
 Part 5: Stored procedure: Investigate related data

Brian Walker
Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.
Copyright 2006 TechTarget
This was last published in January 2006

Dig Deeper on SQL Server Stored Procedures

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.