In this post, I have attempted a crude implementation of Object Oriented SQL Programming using the APPLY operator in SQL Server 2005. I feel that giving some thought in these lines, we can bring in more flexibility, abstraction and reusability to the way we query the database and, may be, create a new style for data access.
There is only one rule that I am going to follow here. All table access will be done using an INLINE TABLE VALUED function. No query will directly access the table. I would like to illustrate it with a small example.
I am going to use the AdventureWorks database and write queries to fetch sales order information. To follow the rules defined, I will be creating two functions to get the order header and detail information respectively.
CREATE FUNCTION GetOrderHeader(@OrderID int) RETURNS TABLE AS RETURN( SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = coalesce(@OrderID,SalesOrderID) ) CREATE FUNCTION GetOrderDetail(@OrderID int,@DetailID int) RETURNS TABLE AS RETURN( SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = coalesce(@OrderID,SalesOrderID) AND SalesOrderDetailID = coalesce(@DetailID,SalesOrderDetailID) )
With the above 2 functions in place, here are some of the queries I can perform to fetch Order Information
--Fetch all order header information select * from GetOrderHeader(NULL) A --Fetch order header information for order ID 43659 select * from GetOrderHeader(43659) A --Fetch all order header/detail information select * from GetOrderHeader(NULL) A CROSS APPLY GetOrderDetail(A.SalesOrderID,NULL) B --Fetch order header/detail information for Order ID 43659 select * from GetOrderHeader(43659) A CROSS APPLY GetOrderDetail(A.SalesOrderID,NULL) B --Fetch information for Order ID 43659 and Detail ID 1 select * from GetOrderHeader(43659) A CROSS APPLY GetOrderDetail(A.SalesOrderID,1) B
Adding another function to fetch the product information
CREATE FUNCTION GetProductInfo(@ProductID int,@Name nvarchar(50)) RETURNS TABLE AS RETURN( SELECT * FROM Production.Product WHERE ProductID = coalesce(@ProductID,ProductID) AND Name like '%' + isnull(@Name,'') + '%' )
I can now search for orders which has a particular product using this query
select * from GetOrderHeader(43659) A CROSS APPLY GetOrderDetail(A.SalesOrderID,NULL) B CROSS APPLY GetProductInfo(B.ProductID,'Mountain') C
According to me, the above query is easier to read and much more maintainable than the one that we will usually write:
SELECT * FROM Sales.SalesOrderHeader A INNER JOIN Sales.SalesOrderDetail B ON A.SalesOrderID = B.SalesOrderID INNER JOIN Production.Product ON B.ProductID = C.ProductID WHERE SalesOrderID = 43659 AND Name like '%Mountain%'
This type of querying through functions has its own advantages.
- For instance, there might be different types of data access that can happen in a particular table(filter, check for existence, etc). Each of this requirement can be a implemented as a seperate function and functions will be written around a particular object, like Orders.
- Sometimes, a table may be normalized for better data access. The function can join the normalized tables, code tables, if any. All these can be abstracted from the query
- Query will be focus on business implementation while the function will focus on data access.