2-6 Views vs Procedures.sql

USE [AdventureWorks2016CTP3];
GO

--View Example
GO

CREATE OR ALTER VIEW [Sales].[vSalesPerson]
AS
    SELECT s.[BusinessEntityID] ,
           p.[Title] ,
           p.[FirstName] ,
           p.[MiddleName] ,
           p.[LastName] ,
           p.[Suffix] ,
           e.[JobTitle] ,
           pp.[PhoneNumber] ,
           pnt.[Name] AS [PhoneNumberType] ,
           ea.[EmailAddress] ,
           p.[EmailPromotion] ,
           a.[AddressLine1] ,
           a.[AddressLine2] ,
           a.[City] ,
           [StateProvinceName] = sp.[Name] ,
           a.[PostalCode] ,
           [CountryRegionName] = cr.[Name] ,
           [TerritoryName] = st.[Name] ,
           [TerritoryGroup] = st.[Group] ,
           s.[SalesQuota] ,
           s.[SalesYTD] ,
           s.[SalesLastYear]
    FROM   [Sales].[SalesPerson] s
           INNER JOIN [HumanResources].[Employee] e ON e.[BusinessEntityID] = s.[BusinessEntityID]
           INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = s.[BusinessEntityID]
           INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = s.[BusinessEntityID]
           INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID]
           INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID]
           INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
           LEFT OUTER JOIN [Sales].[SalesTerritory] st ON st.[TerritoryID] = s.[TerritoryID]
           LEFT OUTER JOIN [Person].[EmailAddress] ea ON ea.[BusinessEntityID] = p.[BusinessEntityID]
           LEFT OUTER JOIN [Person].[PersonPhone] pp ON pp.[BusinessEntityID] = p.[BusinessEntityID]
           LEFT OUTER JOIN [Person].[PhoneNumberType] pnt ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];
GO

SELECT *
FROM   [Sales].[vSalesPerson]
WHERE  BusinessEntityID = 290;

GO
--Procedure Example

CREATE OR ALTER PROCEDURE [Sales].[usp_SalesPerson]
    @busEntityID INT
AS
    SELECT s.[BusinessEntityID] ,
           p.[Title] ,
           p.[FirstName] ,
           p.[MiddleName] ,
           p.[LastName] ,
           p.[Suffix] ,
           e.[JobTitle] ,
           pp.[PhoneNumber] ,
           pnt.[Name] AS [PhoneNumberType] ,
           ea.[EmailAddress] ,
           p.[EmailPromotion] ,
           a.[AddressLine1] ,
           a.[AddressLine2] ,
           a.[City] ,
           [StateProvinceName] = sp.[Name] ,
           a.[PostalCode] ,
           [CountryRegionName] = cr.[Name] ,
           [TerritoryName] = st.[Name] ,
           [TerritoryGroup] = st.[Group] ,
           s.[SalesQuota] ,
           s.[SalesYTD] ,
           s.[SalesLastYear]
    FROM   [Sales].[SalesPerson] s
           INNER JOIN [HumanResources].[Employee] e ON e.[BusinessEntityID] = s.[BusinessEntityID]
           INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = s.[BusinessEntityID]
           INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = s.[BusinessEntityID]
           INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID]
           INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID]
           INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
           LEFT OUTER JOIN [Sales].[SalesTerritory] st ON st.[TerritoryID] = s.[TerritoryID]
           LEFT OUTER JOIN [Person].[EmailAddress] ea ON ea.[BusinessEntityID] = p.[BusinessEntityID]
           LEFT OUTER JOIN [Person].[PersonPhone] pp ON pp.[BusinessEntityID] = p.[BusinessEntityID]
           LEFT OUTER JOIN [Person].[PhoneNumberType] pnt ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID]
    WHERE  s.BusinessEntityID = @busEntityID;
GO


EXEC [Sales].[usp_SalesPerson] @busEntityID = 290;
GO