SQL Server : Recompiling /Refreshing Dependent Objects

Some times we add or drop column to/from a table or view. When the change hits the production we suddenly find out that a lot of Views or Procedures just became invalid and would return wrong result. As for example, the new field that was added is not available in the view that uses select * .

Here is a small stored procedure that you can call after each Alter Table or Alter View statement to make sure that all dependent Views are refreshed and all stored procedures are recompiled.  Basically it finds all dependent objects using build-in stored procedure sp_depends @objectName that takes the object name as input and returns a table of dependent objects that contains two columns 1) Object Name and 2) Object Type. We just go through the result and execute one of the built in stored procs 1) If the object type is view then we call sp_RefreshView @depObjName  2) Otherwise we call sp_Recompile @depObjName.

 

Alter Procedure sp_RefreshDependentObjects
(
@ObjectName as Varchar(100)
)
As

CREATE TABLE #DepObjs(
ObjectName Varchar(100),
ObjectType Varchar(30) )

Declare @objName Varchar(100), @objType Varchar(30)

Insert Into #DepObjs
EXEC sp_depends @ObjectName

declare objCursor CURSOR FOR
Select * From #DepObjs

Open objCursor

Fetch Next From objCursor INTO   @objName, @objType
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @objType=’view’
        Exec sp_RefreshView @objName
    ELSE
        Exec sp_recompile @objName
    FETCH NEXT FROM objCursor INTO @objName, @objType
END

Drop Table #DepObjs

Finding Similar Products

Some times we are required to give a link on a product page to show similar products. The actual sql query that is used to make a list of such products is very simple. You have to make a decision on which fields of the product you want to use to find the similar products. It can be Category, Manufacturer, similar sounding product name and / or some statistical field that you keep about this product in a different table or in the same table. This part that is basically a where condition can just be a matter of choice for you. Lets assume that two product are similar if they have the same Category or Same UserRating or Their names sound similar.

We use self join to make the actual query.

Say for example, if you have a table as follows:

Product

——–

ProductID

ProductName

CategoryID

UserRating

Now lets make the query:

Select p2.ProductID, p2.ProductName, p2.Price, p2.UserRating

From Product  as  p1Inner Join Product  as  p2

ON  p2.CategoryID=p1.CategoryID

OR  p2.UserRating = p1.UserRating

OR Soundex(p2.ProductName)= Soundex(p1.ProductName)

Where

p1.ProductID = @myProductID

Effective Price

Effective Price

Code Example:  (SQL Server / MySQL / Oracle) SQL Server: 1. 1: CREATE View v_EffectivePrice 2: As 3: Select a.EffectiveDate as StartDate , 4: ( 5: Select DateAdd(day,-1,ISNULL(Min(b.EffectiveDate), DateAdd( 6: year,50,GETDATE() ) ) ) 7: From EffectivePrice b Where b.ProductID=a.ProductID And b.EffectiveDate > 8: a.EffectiveDate 9: ) As EndDate, a.ProductID, a.EffectivePrice 10: From EffectivePrice As a 11: 2. Use the view to get price on any date /time 12: Select ep.ProductID, ep.Price from v_EffectivePrice as ep 13: Where @mydate 14: Between ep.StartDate And ep.EndDate And ep.ProductID=@myProductID

Explanation Use case: Store web-fronts need this capability. Requirement: You want the price to be effective from certain date/time and you want to set it up in advance. As for example, you want a promotional price to be effective in a certain date/time . Table Design: EffectivePrice ——————– ProductID int EffectiveDate DateTime Price              Money Primary Key (ProductID, EffectiveDate) Technology Highlight: Self Join