Stored procedure: Examine children
The T-SQL code in Listing 2 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.
You can examine the children of a row in the Purchase table with this stored procedure call:
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).
SELECT R.RegionName
, C.Name
, O.OrderNumber
, O.OrderDate
, I.LineNumber
, I.Quantity
, V.Name
, P.Description
, S.Carrier
, S.TrackingNumber
, S.ShipDate
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
, I.LineNumber
Click for the stored procedures to examine children
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
| 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
|
|
This was first published in January 2006
Join the conversationComment
Share
Comments
Results
Contribute to the conversation