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