Conditionally execute a query

Question

I am in a situation where I have to write a query for a reporting system using SQL Server. Unfortunately, the reporting system can't use SQL with IF statements. So I am trying to write a query where I need to replace an IF EXISTS statement with CASE statements.

But when I do, I get this error:

Msg 116, Level 16, State 1, Line 62
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Msg 116, Level 16, State 1, Line 74
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Regarding my query, the empty GUID below is just a placeholder for a variable that will be coming from the reporting software.

Here is my query:

DECLARE @libID uniqueidentifier = '00000000-0000-0000-0000-000000000000'

SELECT 
    CASE 
        WHEN @libID = '00000000-0000-0000-0000-000000000000' 
            THEN (SELECT fc.libraryID, fc.librarySessionID, 
            CASE 
                WHEN ub.bookTitle IS NOT NULL THEN 'Completed'
            ELSE 'Incomplete'
            END AS completionStatus,
            DATEADD(MINUTE, 0, MIN(eventTimeStamp)) AS timeStarted
            FROM vw_fullCatalog fc INNER JOIN vw_fullReaderList rl ON fc.readerID = rl.readerID 
                LEFT JOIN vw_unavailableBooks ub ON fc.librarySessionID = ub.librarySessionID
            WHERE rl.readerID <> 'ry5'
            GROUP BY fc.libraryID, fc.readerID, fc.librarySessionID, bookTitle)
        ELSE
            (SELECT fc.libraryID, fc.librarySessionID, 
                WHEN ub.bookTitle IS NOT NULL THEN 'Completed'
            ELSE 'Incomplete'
            END AS completionStatus,
            DATEADD(MINUTE, 0, MIN(eventTimeStamp)) AS timeStarted
            FROM vw_fullCatalog fc INNER JOIN vw_fullReaderList rl ON fc.readerID = rl.readerID 
                LEFT JOIN vw_unavailableBooks ub ON fc.librarySessionID = ub.librarySessionID
            WHERE rl.readerID <> 'ry5'
            AND fc.librarySessionID = '1e4b5e3a-1896-4871-af9f-78cb31e946ef'
            GROUP BY fc.libraryID, fc.readerID, fc.librarySessionID, bookTitle)
        END

Does anyone know what the error is referring to?

Thanks!

Answer 1

As explained in the comments, the where clause used below is logically equivalent to the branching construct you are trying to achieve:

SELECT fc.libraryID, fc.librarySessionID, 
  CASE WHEN ub.bookTitle IS NOT NULL THEN 'Completed'
  ELSE 'Incomplete'
  END AS completionStatus,
DATEADD(MINUTE, 0, MIN(eventTimeStamp)) AS timeStarted
FROM vw_fullCatalog fc 
JOIN vw_fullReaderList rl ON fc.readerID = rl.readerID 
LEFT JOIN vw_unavailableBooks ub ON fc.librarySessionID = ub.librarySessionID
WHERE rl.readerID <> 'ry5'
    AND (
     fc.librarySessionID = '1e4b5e3a-1896-4871-af9f-78cb31e946ef' 
     OR @libID = '00000000-0000-0000-0000-000000000000'
    )
GROUP BY fc.libraryID, fc.readerID, fc.librarySessionID, bookTitle;