EA7_spGetClientUsersIDList

            CREATE PROCEDURE [dbo].[EA7_spGetClientUsersIDList](@ClientSitesId integer, @EEOnly bit, @All bit)
              AS BEGIN
                     SET NOCOUNT ON
                     DECLARE @tbl TABLE(
                           ID integer, 
                           UserName nvarchar(50),
                           [Password] nvarchar(255),
                           BackofficeRecordID integer
                     ) 

                     if (@All = 1) -- everybody (undeleted)
                           BEGIN
                                  INSERT INTO @tbl (ID, UserName, [Password], BackofficeRecordID) 
                                  SELECT DISTINCT a.ID, a.UserName, a.password, 0
                                  FROM dbo.ClientUsers a 
                                  LEFT JOIN dbo.BackOfficeSystemUsers b ON b.ClientUsersID = a.ID 
                                  LEFT JOIN dbo.BackOfficeSystemPeople c ON b.BackofficePeopleID = c.ID 
                                  WHERE a.ClientsID = @ClientSitesId AND a.InternalUser = 0 AND a.Deleted = 0  

                                  UPDATE @tbl SET BackofficeRecordID = coalesce(cc.BackofficeRecordID, 0) 
                                  FROM @tbl aa 
                                  INNER JOIN dbo.BackOfficeSystemUsers bb ON bb.ClientUsersID = aa.ID 
                                  INNER JOIN dbo.BackOfficeSystemPeople cc ON bb.BackofficePeopleID = cc.ID 
                                  WHERE cc.BackOfficeSystemID = 1
                           END
                     else 
                           BEGIN
                                  if (@EEOnly = 1)  -- only EE BackOffice users that are CURRENT
                                         INSERT INTO @tbl (ID, UserName, [Password], BackofficeRecordID)  
                                         SELECT a.ID, a.UserName, a.password, c.BackofficeRecordID FROM dbo.ClientUsers a 
                                         INNER JOIN dbo.BackOfficeSystemUsers b ON b.ClientUsersID = a.ID 
                                         INNER JOIN dbo.BackOfficeSystemPeople c ON b.BackofficePeopleID = c.ID 
                                         WHERE a.ClientsID = @ClientSitesId AND a.InternalUser = 0 AND a.Deleted = 0 AND c.BackOfficeSystemID = 1 AND b.[Current] = 1 
                                  ELSE   -- only non-EE BackOffice users that are CURRENT
                                         INSERT INTO @tbl (ID, UserName, [Password], BackofficeRecordID)  
                                         SELECT a.ID, a.UserName, a.password, 0 AS 'BackofficeRecordID' FROM dbo.ClientUsers a 
                                         WHERE a.ClientsID = @ClientSitesId AND a.InternalUser = 0 AND a.Deleted = 0 
                                         AND NOT EXISTS  
                                                (SELECT b.ClientUsersID FROM dbo.BackOfficeSystemUsers b 
                                                INNER JOIN dbo.BackOfficeSystemPeople c ON b.BackofficePeopleID = c.ID 
                                                WHERE c.BackOfficeSystemID = 1 AND b.[Current] = 1 AND a.ID = b.ClientUsersID) 
                           END
                     SELECT * FROM @tbl ORDER BY UserName
              END