(c) 2003 Visual Studio Magazine 
Fawcette Technical Publications

T-SQL	Start With a Stored Procedure
Listing 1	This stored procedure (spOrders1) nests Customers within Orders, Shippers within Customers, and OrderDetails within Shippers. Bold type identifies field-list elements that generate the defective nesting (except OrderDetails.OrderID). If you use SQL Server's graphical design tools, such as Access 2000+'s Project Designer, you must edit the stored procedure's SQL statement to remove the default Expr1 alias from the OrderDetails.OrderID field-list element. Two instances of OrderID fields occur when you execute the stored procedure in SQL Query Analyzer. The Project Designer labels the second instance Column0.

CREATE PROCEDURE dbo.spOrder1
(@OrderID int)
AS 
SET NOCOUNT ON 

SELECT Orders.OrderID, Orders.CustomerID, 
Customers.CompanyName, Customers.ContactName, 
Customers.ContactTitle, Customers.Address, 
Customers.City, Customers.Region, 
Customers.PostalCode, Customers.Country, 
Customers.Phone, Customers.Fax, 
Orders.EmployeeID, 
Employees.FirstName + ' ' + Employees.LastName AS 
EmployeeName, Orders.OrderDate, 
Orders.RequiredDate, Orders.ShippedDate, 
Orders.ShipVia, Shippers.CompanyName 
AS ShipperName, Orders.Freight, Orders.ShipName, 
Orders.ShipAddress, Orders.ShipCity, 
Orders.ShipRegion, Orders.ShipPostalCode, 
Orders.ShipCountry, 
OrderDetails.OrderID, OrderDetails.Quantity, 
OrderDetails.ProductID, Products.ProductName, 
Products.QuantityPerUnit, OrderDetails.UnitPrice, 
Products.UnitPrice AS ListPrice, 
OrderDetails.Discount, 
CONVERT(money, (OrderDetails.Quantity * OrderDetails.UnitPrice)  
* (1 - OrderDetails.Discount)) AS Extended 

FROM Customers 
INNER JOIN Orders ON Customers.CustomerID = 
Orders.CustomerID 
INNER JOIN [Order Details] AS OrderDetails ON 
Orders.OrderID = OrderDetails.OrderID 
INNER JOIN Products ON OrderDetails.ProductID = 
Products.ProductID 
INNER JOIN Shippers ON Orders.ShipVia = 
Shippers.ShipperID 
INNER JOIN Employees ON Orders.EmployeeID = 
Employees.EmployeeID

WHERE (Orders.OrderID = @OrderID)

T-SQL	Alter the Stored Procedure's Field List.
Listing 2	Change the sequence of OrderID and CustomerID fields and the table source of CustomerID from Orders to Customers to make Customers the parent node of Orders. Apply innocuous functions and add aliases to eliminate lookup table nodes, such as Shippers and Products in this example. You can eliminate the Customers node by retaining Orders.CustomerID, removing Customers.CustomerID, and applying the T-SQL RTRIM function to all remaining Customers fields.

CREATE PROCEDURE spOrder2
(@OrderID int)
AS 
SET NOCOUNT ON 

SELECT Customers.CustomerID, Customers.CompanyName, 
Customers.ContactName, Customers.ContactTitle, 
Customers.Address, Customers.City, 
Customers.Region, Customers.PostalCode, 
Customers.Country, Customers.Phone, 
Customers.Fax, Orders.OrderID, Orders.EmployeeID, 
Employees.FirstName + ' ' + Employees.LastName 
AS EmployeeName, Orders.OrderDate, 
Orders.RequiredDate, Orders.ShippedDate, 
Orders.ShipVia, 
RTRIM(Shippers.CompanyName) AS ShipperName, 
Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, 
Orders.ShipRegion, Orders.ShipPostalCode, 
Orders.ShipCountry, OrderDetails.OrderID, OrderDetails.Quantity, OrderDetails.ProductID, 
RTRIM(Products.ProductName) 
AS ProductName, RTRIM(Products.QuantityPerUnit) AS QuantityPerUnit, OrderDetails.UnitPrice, 
ROUND(Products.UnitPrice, 2) AS ListPrice, 
OrderDetails.Discount, CONVERT(money, 
(OrderDetails.Quantity * 
OrderDetails.UnitPrice) * 
(1 - OrderDetails.Discount)) AS Extended

FROM Customers 
INNER JOIN Orders ON Customers.CustomerID = 
Orders.CustomerID 
INNER JOIN [Order Details] AS OrderDetails ON Orders.OrderID = 
OrderDetails.OrderID 
INNER JOIN Products ON OrderDetails.ProductID = 
Products.ProductID 
INNER JOIN Shippers ON Orders.ShipVia = Shippers.ShipperID 
INNER JOIN Employees ON Orders.EmployeeID = 
Employees.EmployeeID 

WHERE (Orders.OrderID = @OrderID)

