text: 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