You can examine the children of a row in the Purchase table with this stored procedure call:
Click for the stored procedures to examine children
The T-SQL code in creates a system stored procedure named sp_ExamineChildren. The stored procedure accepts two required parameters, a table name (@DBTable) and a primary key value (@DBValue). The routine returns a result set containing a row for every child table. Each row includes a child table name and the number of rows in the table that relate to the parent table row specified by the parameters. This stored procedure might be used for simple data analysis. A SUM aggregation on the number of rows column can determine whether the parent row has any child rows. If the sum is greater than zero then child rows exist and the parent row must not be deleted.
EXECUTE sp_ExamineChildren 'Purchase',3
The query below returns a set of data containing purchase details for a certain customer. The query involves eight tables with a variety of parent/child relationships. The result is a denormalized set of data suitable for a report. However, the result may not be much use to a DBA or developer because the source of each column is not apparent in the output. A problem could easily be concealed among all the redundant data (the same parent data repeated with each child row).
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
FROM Customer AS C
JOIN Region AS R
ON C.RegionID = R.RegionID
JOIN Purchase AS O
ON C.CustomerID = O.CustomerID
JOIN PurchaseItem AS I
ON O.PurchaseID = I.PurchaseID
JOIN Product AS P
ON I.ProductID = P.ProductID
JOIN Vendor AS V
ON P.VendorID = V.VendorID
JOIN PurchaseItemShipment AS E
ON I.PurchaseItemID = E.PurchaseItemID
JOIN Shipment AS S
ON E.ShipmentID = S.ShipmentID
WHERE C.CustomerID = 2
ORDER BY O.OrderNumber
A surrogate key architecture for powerful database operations
Why use surrogate keys
A surrogate key architecture
Stored procedures: Create and delete constraints and indexes
Stored procedure: Examine children
Stored procedure: Investigate related data
|ABOUT THE AUTHOR:
| 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