uspGetEmployeeDetail

USE [AdventureWorks2008]
GO
/****** Object:  StoredProcedure [dbo].[uspGetEmployeeDetail]    Script Date: 07/13/2009 20:59:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Steef-Jan Wiggers
-- Create date: 14-09-2009
-- Description:	Get detail information of employee
--				based on national id number
-- =============================================
ALTER PROCEDURE [dbo].[uspGetEmployeeDetail] 
	-- Add the parameters for the stored procedure here
	@NationalIDNumber As nVARCHAR(15)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT     Person.Person.FirstName, Person.Person.MiddleName, Person.Person.LastName, HumanResources.Employee.JobTitle,  
                      HumanResources.Employee.MaritalStatus, HumanResources.Employee.Gender, Person.Address.AddressLine1, Person.Address.City, Person.Address.PostalCode, 
                      Person.EmailAddress.EmailAddress, Person.PersonPhone.PhoneNumber, HumanResources.Employee.NationalIDNumber
FROM         HumanResources.Employee INNER JOIN
                      Person.Person ON HumanResources.Employee.BusinessEntityID = Person.Person.BusinessEntityID INNER JOIN
                      Person.BusinessEntityAddress ON HumanResources.Employee.BusinessEntityID = Person.BusinessEntityAddress.BusinessEntityID INNER JOIN
                      Person.Address ON Person.BusinessEntityAddress.AddressID = Person.Address.AddressID INNER JOIN
                      Person.EmailAddress ON Person.Person.BusinessEntityID = Person.EmailAddress.BusinessEntityID INNER JOIN
                      Person.PersonPhone ON Person.Person.BusinessEntityID = Person.PersonPhone.BusinessEntityID
WHERE		HumanResources.Employee.NationalIDNumber = @NationalIDNumber 
END