CREATE PROCEDURE dbo.db_GetUsers
	-- Gets all ACTIVE users
	@ChatRoomID int
AS

	DECLARE @WHERE_CLAUSE varchar(255)

	If @ChatRoomID > 0
		BEGIN
			SET @WHERE_CLAUSE = 'dbo.Chatters.isActive = 1 AND dbo.Chatters.ChatId ='  + @ChatRoomID
		END
	ELSE
		BEGIN
			SET @WHERE_CLAUSE = 'dbo.Chatters.isActive = 1'
		END

	SELECT     dbo.Chatters.cName, dbo.Chatters.id, dbo.Chatters.lastAction, dbo.Roles.Type, dbo.ChatSettings.Name
	FROM         dbo.Chatters INNER JOIN
	                      dbo.ChatSettings ON dbo.Chatters.ChatId = dbo.ChatSettings.id INNER JOIN
	                      dbo.Roles ON dbo.Chatters.RoleID = dbo.Roles.id
	WHERE   @WHERE_CLAUSE ORDER BY dbo.ChatSettings.Name ASC

 

CREATE PROCEDURE dbo.db_GetUsers (@ChatRoomID int=NULL)
AS
SET NOCOUNT ON 

       SELECT c.cName, dbo.c.id, c.lastAction, r.Type,cs.Name
	 FROM dbo.Chatters c 
   INNER JOIN dbo.ChatSettings cs ON c.ChatId = cs.id 
   INNER JOIN dbo.Roles r ON c.RoleID = r.id
        WHERE c.isActive = 1
          AND c.ChatId = ISNULL(@ChatRoomID,c.ChatId)
     ORDER BY cs.Name ASC

GO

CREATE PROCEDURE dbo.db_GetUsers (@ChatRoomID int=NULL)
AS
SET NOCOUNT ON 

IF @ChatRoomID IS NULL
BEGIN

       SELECT c.cName, dbo.c.id, c.lastAction, r.Type,cs.Name
	 FROM dbo.Chatters c 
   INNER JOIN dbo.ChatSettings cs ON c.ChatId = cs.id 
   INNER JOIN dbo.Roles r ON c.RoleID = r.id
        WHERE c.isActive = 1
     ORDER BY cs.Name ASC
END
ELSE
BEGIN

       SELECT c.cName, dbo.c.id, c.lastAction, r.Type,cs.Name
	 FROM dbo.Chatters c 
   INNER JOIN dbo.ChatSettings cs ON c.ChatId = cs.id 
   INNER JOIN dbo.Roles r ON c.RoleID = r.id
        WHERE c.isActive = 1 and c.ChatId = @ChatRoomID
     ORDER BY cs.Name ASC
END
GO