Return two Count(*) results in one select

Question

I have a stored procedure and I want it to return the following...

TotalItems  |  FailedItems
@totalItems | @failedItems

where --> @totalItems = `SELECT COUNT(*)
    From dbo.OdsBuild AS P 
    where P.StartTime Between @StartDate and @EndDate 
    AND P.Official = 1`

where --> @failedItems = `SELECT COUNT(*)
    From dbo.Items AS P
    where p.StartTime Between @StartDate and @EndDate 
    AND P.Official = 1 AND ( P.Result = 7 OR P.Result = 8 OR P.Result = 14)`

Answer 1

Subquery the SELECT COUNTs

SELECT
    (SELECT COUNT(*)
        From dbo.OdsBuild AS P 
        where P.StartTime Between @StartDate and @EndDate 
        AND P.Official = 1) totalItems ,
    (SELECT COUNT(*)
        From dbo.Items AS P
        where p.StartTime Between @StartDate and @EndDate 
        AND P.Official = 1 AND ( P.Result = 7 OR P.Result = 8 OR P.Result = 14)) failedItems

If you already had them set as variables, of course you don't have to repeat the SELECT COUNTs.

SELECT @totalItems AS totalItems, @failedItems AS failedItems

SELECT statements are allows to stand alone without FROM clauses.

Answer 2

Can't you simply select those variables at the end of your proc?

SELECT @totalitems AS TotalItems, @faileditems AS FailedItems