Starting with a new proc to select data of off the EM, Employee Custom Tab Fields and a union with a Auxilary data table to add in such things as printers, conference rooms, local police, fire etc. I will build on this stored procedure defining a datagrid with filter controls in a Sharepoint webpart.
Virtualized Dev Environment: SQL 2005, Windows 2003 server, Visual Studio 2008, Sharepoint Extensions
This proc allows wildcard searches on fields and does not filter if a null is passed. The union to the auxiliary table poses some challenges so I created a UDF dbo.IgnoreNullParm to return ‘1′ or ‘0′ to effectively turn off the union ‘leg’ when we are passed a filter parm that does not exist for that table (i.e. Initials).
Proc Def:
Create Procedure [dbo].[searchEmployees]
(
@FirstName nvarchar(50)
,@LastName nvarchar(50)
,@Initials nvarchar(03)
,@Phone nvarchar(20)
,@Office nvarchar(50)
,@Dept nvarchar(50)
,@EmpNum nvarchar(04)
)
as
Select
EM.Employee
, isNull(EM.PreferredName,EM.FirstName) as FirstName
, (RTRIM(EM.LastName) + ' ' + isNull(Suffix,'')) as LastName
, EM.MiddleName
, EM.Org as PrimaryDept
, EM.Region
, EM.WorkPhone
, EM.WorkPhoneExt
, EM.MobilePhone
, ECTF.Cust5ULPhysical as Office
, ECTF.custSecondaryDepartment as SecondaryDept
From
EM
Join EmployeeCustomTabFields ECTF on ( ECTF.Employee = EM.Employee )
Where
EM.Status = 'A'
And ( EM.FirstName like Case When (@FirstName is null) Then '%' Else RTRIM(@FirstName) + '%' End
OR EM.PreferredName like Case When (@FirstName is null) Then '%' Else RTRIM(@FirstName) + '%' End
)
And EM.LastName like Case When (@LastName is null) Then '%' Else RTRIM(@LastName) + '%' End
And SUBSTRING(EM.FirstName,1,1)
+ RTRIM(Case When(DATALENGTH(EM.MiddleName) = 0) Then '' Else SUBSTRING(EM.MiddleName,1,1) End )
+ SUBSTRING(EM.LastName,1,1) LIKE Case When (@Initials is null) Then '%' Else @Initials End
And ( EM.WorkPhone like '%' + isNull(@Phone,'%') + '%'
OR EM.WorkPhoneExt like '%' + isNull(@Phone,'%') + '%'
OR EM.MobilePhone like '%' + isNull(@Phone,'%') + '%' )
And ECTF.Cust5ULPhysical = isNull(@Office, ECTF.Cust5ULPhysical)
And ( EM.Org like '%' + isNull(@Dept,'%') + '%'
OR ECTF.custSecondaryDepartment like '%' + isNull(@Dept,'%') + '%' )
And EM.Employee like isNull(@EmpNum,'%') + '%'
UNION ALL
Select EmpNum as Employee
, FirstName
, LastName
, '' as MiddleName
, '' as PrimaryDept
, '' as Region
, Phone as WorkPhone
, PhoneExt as WorkPhoneExt
, '' as MobilePhone
, ECTF_Cust5ULPhysical as Office
, '' as SecondaryDept
From OfficeAux OA
Where
OA.FirstName like Case When (@FirstName is null) Then '%' Else RTRIM(@FirstName) + '%' End
And OA.LastName like Case When (@LastName is null) Then '%' Else RTRIM(@LastName) + '%' End
And '1' = dbo.IgnoreNullParm(@Initials) <em> -- if parm is null ignore it otherwise this effectively 'turns off union' if parm is not null</em>
And '1' = dbo.IgnoreNullParm(@Dept) <em>-- if parm is null ignore it otherwise this effectively 'turns off union' if parm is not null</em>
And ( OA.Phone like '%' + isNull(@Phone,'%') + '%'
OR OA.PhoneExt like '%' + isNull(@Phone,'%') + '%')
And ECTF_Cust5ULPhysical = isNull(@Office, ECTF_Cust5ULPhysical)
And EmpNum like isNull(@EmpNum,'%') + '%'
UDF
Create function [dbo].[IgnoreNullParm]
(
@Field nvarchar(50)
)
Returns bit
-- Sends back True '1' if Parm is null
-- Setup in where clause as Where '1' = IgnoreNullParm(@parm)
-- Allows you to effectively turn off a union 'leg' if a parm is not null and ignore it if not null
as
Begin
Declare @bReturn as bit
Select @bReturn = Case When (@Field is null) Then '1' Else '0' End
Return @bReturn
End
Auxilary Table:
CREATE TABLE [dbo].[OfficeAux](
[ID] [uniqueidentifier] NOT NULL,
[ECTF_Cust5ULPhysical] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[XName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpNum] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PhoneExt] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MobilePhone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[isActive] [bit] NULL,
[lastUpdated] [datetime] NULL,
[lastUpdater] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
End
) on our intranet with links to their picture. Easily moved to Sharepoint via SmartPart
