T-SQL – Views and Inline Functions

View can be used to hide complicated query. You can give access to the view, but not to the table used by the view. Only select used to create the view is stored, data (rows) is not. View cannot use temporary tables. It is parameterless.

CREATE VIEW Production.CategorizedProducts
WITH SCHEMABINDING, -- table which is used in view cannot be modified without dropping the view
ENCRYPTION -- select used to create view cannot be displayed
AS
(
SELECT P.productname, C.categoryname, P.unitprice
FROM Production.Products P
JOIN Production.Categories C on P.categoryid = C.categoryid
WHERE C.categoryname IN ('Beverages', 'Seafood')
--order by clause is not supported
)
WITH CHECK OPTION; -- you can add rows through this view, but only when the newly created row meets the where clause condition

SELECT * FROM Production.CategorizedProducts
ORDER BY categoryname, productname;

ALTER VIEW Production.CategorizedProducts
AS
(
SELECT P.productname, C.categoryname, P.unitprice, P.discontinued
FROM Production.Products P
JOIN Production.Categories C on P.categoryid = C.categoryid
WHERE C.categoryname = 'Beverages'
);

DROP VIEW Production.CategorizedProducts;

Inline function is similiar to the view, but parameters can be passed into it’s body.

CREATE FUNCTION Production.CategorizedProducts(@categoryname nvarchar(50))
RETURNS TABLE
AS 
RETURN
(
SELECT P.productname, C.categoryname, P.unitprice, P.discontinued
FROM Production.Products P
JOIN Production.Categories C on P.categoryid = C.categoryid
WHERE C.categoryname = @categoryname -- parameter passed to the query
);

SELECT * FROM Production.CategorizedProducts(N'Beverages');

DROP FUNCTION Production.CategorizedProducts;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s