SQL Server: How to Create a Parameterized Views


In SQL Server functionality of parametrized views can beachieved 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 theparameters for the function here
       @NationalIDNumber VARCHAR(9)
)
RETURNS TABLE
AS
RETURN
(
       — Add theSELECT statement with parameter references here
       SELECT   e.BusinessEntityID, p.Title, p.FirstName, p.MiddleName,
              p.LastName, p.Suffix, e.JobTitle, pp.PhoneNumber,
              pnt.NameAS PhoneNumberType,ea.EmailAddress,
              p.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City,
              sp.NameAS StateProvinceName,a.PostalCode,
              cr.NameAS CountryRegionName,
              p.AdditionalContactInfo
       FROM            HumanResources.Employee AS e INNERJOIN
              Person.Person AS p
              ONp.BusinessEntityID =e.BusinessEntityID INNERJOIN
              Person.BusinessEntityAddressAS bea
              ONbea.BusinessEntityID =e.BusinessEntityID INNERJOIN
              Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN
              Person.StateProvince AS sp
              ONsp.StateProvinceID =a.StateProvinceID INNERJOIN
              Person.CountryRegion AS cr
              ONcr.CountryRegionCode =sp.CountryRegionCode LEFTOUTER JOIN
              Person.PersonPhone AS pp
              ONpp.BusinessEntityID =p.BusinessEntityID LEFTOUTER JOIN
              Person.PhoneNumberType AS pnt
              ONpp.PhoneNumberTypeID =pnt.PhoneNumberTypeID LEFTOUTER JOIN
              Person.EmailAddress AS ea
              ONp.BusinessEntityID =ea.BusinessEntityID
       WHERE e.NationalIDNumber = @NationalIDNumber
)
GO
How to use it. Very Simple 🙂
SELECT * FROM PV_GetEmployeeInformationBySSN (‘112457891’)
Advertisements

Posted on November 18, 2011, in Sql Server 2008, SQL Server 2012, SQL Server Basics, SQL Server Denali, TSQL Tips n Tricks, View. Bookmark the permalink. 2 Comments.

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

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

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

%d bloggers like this: