Friday, November 18, 2011

SQL Server: How to Create a Parameterized Views

In SQL Server functionality of parametrized views can be achieved by creating an in-line table valued function. Let’s see how to convert a commonly used view HumanResources.vEmployee in AdventureWorks to a parametrized view.

CREATE FUNCTION PV_GetEmployeeInformationBySSN
       -- Add the parameters for the function here
       @NationalIDNumber VARCHAR(9)
       -- Add the SELECT statement with parameter references here
       SELECT   e.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,
              sp.Name AS StateProvinceName, a.PostalCode,
              cr.Name AS CountryRegionName,
       FROM            HumanResources.Employee AS e INNER JOIN
              Person.Person AS p
              ON p.BusinessEntityID = e.BusinessEntityID INNER JOIN
              Person.BusinessEntityAddress AS bea
              ON bea.BusinessEntityID = e.BusinessEntityID INNER JOIN
              Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN
              Person.StateProvince AS sp
              ON sp.StateProvinceID = a.StateProvinceID INNER JOIN
              Person.CountryRegion AS cr
              ON cr.CountryRegionCode = sp.CountryRegionCode LEFT OUTER JOIN
              Person.PersonPhone AS pp
              ON pp.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN
              Person.PhoneNumberType AS pnt
              ON pp.PhoneNumberTypeID = pnt.PhoneNumberTypeID LEFT OUTER JOIN
              Person.EmailAddress AS ea
              ON p.BusinessEntityID = ea.BusinessEntityID
       WHERE e.NationalIDNumber = @NationalIDNumber

How to use it. Very Simple :)
SELECT * FROM PV_GetEmployeeInformationBySSN ('112457891')


  1. that is not a view. Its a function with table return type

  2. It is not view, but it works almost like one.


All suggestions are welcome