spGetMessageCounts_Filtered

CREATE PROCEDURE [dbo].[spGetMessageCounts_Filtered]
(
    @OwnerAccountID int,
    @MessageTypeFilter int
)
    AS

    DECLARE    @Read int
    DECLARE    @UnRead int

        --DRAFTS
        if @MessageTypeFilter = 0
        BEGIN

        SELECT 
        @Read = SUM(CASE o.Viewed
         WHEN 1 THEN 1
         ELSE 0
         END),
        @UnRead = SUM(CASE o.Viewed
         WHEN 0 THEN 1
         ELSE 0
         END)
        FROM [Message] o
        WHERE o.OwnerID = @OwnerAccountID
        AND (o.RecipientGroupID = 0 OR o.RecipientGroupID IS NULL)
        AND o.SenderID = o.OwnerID
        AND (o.OwnerID <> o.RecipientID OR o.RecipientID IS NULL OR o.RecipientID = 0)
        AND o.Sent = 0 AND o.Deleted = 0
        AND (o.MessageFolderID = 0 or o.MessageFolderID IS NULL)

        END


        --SENT
        if @MessageTypeFilter = 1
        BEGIN

        SELECT 
        @Read = SUM(CASE o.Viewed
         WHEN 1 THEN 1
         ELSE 0
         END),
        @UnRead = SUM(CASE o.Viewed
         WHEN 0 THEN 1
         ELSE 0
         END)
        FROM [Message] o
        WHERE o.OwnerID  = @OwnerAccountID
        AND o.SenderID = o.OwnerID
        AND o.Sent = 1 AND o.Deleted = 0
        AND (o.MessageFolderID = 0 or o.MessageFolderID IS NULL)

        END

        --RECEIVED
        if @MessageTypeFilter = 2
        BEGIN

        SELECT 
        @Read = SUM(CASE o.Viewed
         WHEN 1 THEN 1
         ELSE 0
         END),
        @UnRead = SUM(CASE o.Viewed
         WHEN 0 THEN 1
         ELSE 0
         END)
        FROM [Message] o
        WHERE o.OwnerID  = @OwnerAccountID
        AND o.RecipientID = o.OwnerID
        AND o.Deleted = 0
        AND (o.MessageFolderID = 0 or o.MessageFolderID IS NULL)

        END

        --TRASH
        if @MessageTypeFilter = 3
        BEGIN

        SELECT 
        @Read = SUM(CASE i.Viewed WHEN 1 THEN 1 ELSE 0 END),  
        @UnRead = SUM(CASE i.Viewed WHEN 1 THEN 0 ELSE 1 END)
        FROM
        (SELECT viewed FROM message WHERE OwnerID = @OwnerAccountID and Deleted = 1) as i

        END

        --PERSONAL FOLDERS
        if @MessageTypeFilter = 4
        BEGIN

        SELECT 
        @Read = SUM(CASE i.Viewed WHEN 1 THEN 1 ELSE 0 END),  
        @UnRead = SUM(CASE i.Viewed WHEN 1 THEN 0 ELSE 1 END)
        FROM
        (SELECT viewed FROM message WHERE OwnerID = @OwnerAccountID and Deleted = 0 AND MessageFolderID > 0) as i

        END


        SELECT    ISNULL(@Read, 0) 'Read',
                ISNULL(@UnRead, 0) 'UnRead'