Retrieving Two Values from a Stored Procedure

Question

Can someone tell me how I can combine the two SQL statements that count the number of messages and number of unread messages? Its ineffecient two have two statements but I don't know what to search for to get the answer im looking for. Thanks in advance.

CREATE PROCEDURE dbo.GetMessages (
  @username nchar(12),
  @isCount bit,
  @message_count int OUTPUT,
  @unread_message_count int OUTPUT
) AS

IF @isCount = 1
BEGIN
  SET @message_count = 
  (
    SELECT        COUNT(*)
    FROM          messages
    WHERE         usernameTo = @username
  )

  SET @unread_message_count =
  (
    SELECT        COUNT(*)
    FROM          messages
    WHERE         usernameTo = @username AND message_read = 1
  )
END
ELSE
BEGIN
  SELECT        *
  FROM          messages
  WHERE         usernameTo = @username
END

Answer 1

select count(*) as TotalCount, 
    count(case when message_read = 1 then 1 else null end) as ReadMessageCount, 
    count(case when message_read <> 1 then 1 else null end) as UnreadMessageCount
from messages
where usernameTo = 'jsmith'

Answer 2

It depends on the SQL you're using, but, for instance, in PostgreSQL it would be like this:

SELECT
    COUNT(*) AS messages,
    COUNT( CASE WHEN message_read=1 THEN 1 ELSE NULL END ) AS unreads,
    COUNT( CASE WHEN message_read=1 THEN NULL ELSE 1 END ) AS reads
FROM messages WHERE usernameTo='someone';

That should help.

Answer 3

SELECT COUNT(*) AS total_messages,      
       SUM(CASE WHEN message_read=1 THEN 1 END) as read, 
       SUM(CASE WHEN message_read=0 THEN 1 END) as unread
FROM mesages WHERE usernameTo='username';

Answer 4

First, you'll want this to be split into two stored procedures. If you are ever using a bit to determine which chunk of code to execute, you're probably trying to put too much into one stored procedure.

CREATE PROCEDURE dbo.GetMessageCount (
  @username nchar(12),
  @message_count int OUTPUT,
  @unread_message_count int OUTPUT
) AS

SELECT
    @message_count = SUM(CASE WHEN message_read = 1 THEN 1 ELSE 0 END),
    @unread_message_count = SUM(CASE WHEN message_read <> 1 THEN 1 ELSE 0 END)
FROM messages
WHERE usernameTo = @username

GO

You could do something like this for the stored procdure that will get all messages for a specific user. You don't want to use "Select *", here. You should always list the specific columns you need. I listed a few example columns.

CREATE PROCEDURE dbo.GetMessagesByUser (
    @username nchar(12)
) AS

  SELECT
      MessageID,
      MessageSubject,
      MessageContent,
      MessageFromUser,
      MessageCreatedDate
  FROM          messages
  WHERE         usernameTo = @username

GO