For one of the projects I have worked on recently, a set of variables needed to be computed for each product and for each month of the year on a regular basis.
The product data was stored in SQL Server 2005 SP2.
The computed volume was quite large since around 60 variables x 120 products x 12 months = 86,400 results needed to be calculated every time and be stored in the result table.
The business requirements were the following:
The calculated variables were based on complex business rules which were quite tricky to be implemented in TSQL.
Each variable related to a product had to use input from various tables.
Variables related to a given product were independent of other products.
On the other hand, variables were sometimes based on others variables for the same product.
It was decided to express the business rules using a .NET language such as C# and to create a Table-Valued Function (TVF) using the CLR. Srinivas Sampath wrote a good introduction on how to implement a TVF in .NET.
sp_help is a stored procedure introduced by SQL Server 2000 which reports information about any database object. For example, executing the following SQL command in the context of Northwind will return information related to the table Customers.
Pulling data from the database often requires to write complex SQL statements. Similar statements are often constructed by data access objects.
The use of stored procedures deployed on the database server is sometimes the solution to factorize and centralized these SQL statements. However, with complex business rules changing quite often, generating SQL statements through code is often the most simple and fatest solution available.
Let's take an example of this following DAO pulling the list of the customers:
