Dynamic SQL View using CONTEX_INFO

SQL Server supports creating views and I hope all knows what is a view. According to wiki its just a result set of query on data. Users can access it just like another table but it doesn't contain its own data like table. If we google for different between view and table we can get ton of links.

Below is a normal view definition.

---

CREATE VIEW CreditCards 
AS 
  (SELECT * 
   FROM   adventureworks2012.sales.creditcard 
   WHERE  cardtype = 'Vista')

---

In our day to day life most of the views we are creating has a static 'where' clause. Here we are
going to see how that part can be dynamic.

Create view by getting values from CONTEXT_INFO

CONTEXT_INFO gives us option to store values in the context of a session. It can be more compared to a static variable in programming. Here we are going to see how a view can be created which is affected by CONTEXT_INFO value.

Below is a sample view which is consuming the CONTEXT_INFO

---

CREATE VIEW creditcards 
AS 
  (SELECT * 
   FROM   adventureworks2012.sales.creditcard 
   WHERE  CardType = CONVERT(VARCHAR(25), Context_info())) 

---

If somebody call this view without setting the CONTEXT_INFO it will return the view which matches the where condition(CardType) with NULL. Else it returns proper data set.

Setting the CONTEXT_INFO

If we have already a view defined we can set the CONTEXT_INFO to our own values and select the view. The view will return proper values.

---

DECLARE @contextInfo VARBINARY(128) 

SET @contextInfo = CONVERT(VARBINARY(128), 'Vista') 
SET context_info @contextInfo 

SELECT * 
FROM   adventureworks2012.dbo.creditcards

---

The above query will return all the Sales.CreditCard with CardType='Vista'

One advantage is we can set this CONTEXT_INFO from code. More precisely from central data access before executing any other query. This way we can control centrally, what the tracks are getting from this view if they go via common data access.

http://jasondentler.com/blog/2010/01/exploiting-context_info-for-fun-and-audit/