|
Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor for demonstrating the application of CREATE VIEW statement.
-- SQL Server CREATE VIEW statement
USE AdventureWorks2008;
GO
CREATE VIEW vProductLocation
AS
SELECT P.Name AS Product,
L.Name AS InventoryLoc,
SUM(PI.Quantity) AS QtyAvailable
FROM Production.Product AS P
INNER JOIN Production.ProductInventory AS PI
ON P.ProductID = PI.ProductID
INNER JOIN Production.Location AS L
ON PI.LocationID = L.LocationID
GROUP BY P.Name,
L.Name
GO
-- Test view with SELECT - notice ORDER BY is here not in view definition
SELECT TOP(5) * FROM vProductLocation
ORDER BY NEWID()
GO
/* Product InventoryLoc QtyAvailable
Bearing Ball Miscellaneous Storage 318
Internal Lock Washer 4 Miscellaneous Storage 603
ML Mountain Seat/Saddle Subassembly 72
LL Mountain Front Wheel Subassembly 457
LL Mountain Frame - Black, 40 Debur and Polish 129 */
------------
-- T-SQL CREATE VIEW statement
CREATE VIEW vWorkOrder
AS
SELECT WorkOrderID,
P.Name AS Product,
OrderQty,
CONVERT(DATE,DueDate) AS DueDate
FROM Production.WorkOrder W
INNER JOIN Production.Product P
ON W.ProductID = P.ProductID
WHERE P.ProductSubcategoryID IN (2,3)
GO
-- Test view with random SELECT
SELECT TOP(3) * FROM vWorkOrder
ORDER BY NEWID()
GO
/* WorkOrderID Product OrderQty DueDate
34021 Road-550-W Yellow, 44 1 2003-06-04
27710 Road-650 Black, 58 120 2003-02-15
61003 Road-750 Black, 52 1 2004-03-28 */
------------
------------
-- View from a stored procedure result set
------------
CREATE VIEW vSPWHO
AS
SELECT * FROM OPENQUERY(YOURSERVER, 'exec sp_who')
GO
SELECT * FROM vSPWHO
GO
-- (131 row(s) affected)
------------
Related article:
CREATE VIEW (Transact-SQL)
http://sqlusa.com/bestpractices2005/selectfromsproc/
|
