Manage Learn to apply best practices and optimize your operations.

Stored procedure: Examine children

Brian Walker offers a stored procedure for examining child rows.

Listing 2

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).

    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

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

Dig Deeper on SQL Server Stored Procedures

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.