USE [AdventureWorks2008]<\/div>
GO<\/div>
/****** Object: StoredProcedure [dbo].[uspGetEmployeeDetail] Script Date: 07/13/2009 20:59:57 ******/<\/div>
SET ANSI_NULLS ON<\/div>
GO<\/div>
SET QUOTED_IDENTIFIER ON<\/div>
GO<\/div>
-- =============================================<\/div>
-- Author: Steef-Jan Wiggers<\/div>
-- Create date: 14-09-2009<\/div>
-- Description: Get detail information of employee<\/div>
-- based on national id number<\/div>
-- =============================================<\/div>
ALTER PROCEDURE [dbo].[uspGetEmployeeDetail] <\/div>
-- Add the parameters for the stored procedure here<\/div>
@NationalIDNumber As nVARCHAR(15)<\/div>
AS<\/div>
BEGIN<\/div>
-- SET NOCOUNT ON added to prevent extra result sets from<\/div>
-- interfering with SELECT statements.<\/div>
SET NOCOUNT ON;<\/div>
<\/div>
-- Insert statements for procedure here<\/div>
SELECT Person.Person.FirstName, Person.Person.MiddleName, Person.Person.LastName, HumanResources.Employee.JobTitle, <\/div>
HumanResources.Employee.MaritalStatus, HumanResources.Employee.Gender, Person.Address.AddressLine1, Person.Address.City, Person.Address.PostalCode, <\/div>
Person.EmailAddress.EmailAddress, Person.PersonPhone.PhoneNumber, HumanResources.Employee.NationalIDNumber<\/div>
FROM HumanResources.Employee INNER JOIN<\/div>
Person.Person ON HumanResources.Employee.BusinessEntityID = Person.Person.BusinessEntityID INNER JOIN<\/div>
Person.BusinessEntityAddress ON HumanResources.Employee.BusinessEntityID = Person.BusinessEntityAddress.BusinessEntityID INNER JOIN<\/div>
Person.Address ON Person.BusinessEntityAddress.AddressID = Person.Address.AddressID INNER JOIN<\/div>
Person.EmailAddress ON Person.Person.BusinessEntityID = Person.EmailAddress.BusinessEntityID INNER JOIN<\/div>
Person.PersonPhone ON Person.Person.BusinessEntityID = Person.PersonPhone.BusinessEntityID<\/div>
WHERE HumanResources.Employee.NationalIDNumber = @NationalIDNumber <\/div>
END<\/div><\/pre><\/div>\n \n <\/div>\n\n