sql - Selecting columns that are not all null

Question

So I have a query that looks like this -

select 
case when @subType = 1 or @subType = 2 then id end as Id,
case when  @subType = 3 then name end as name
case when @subType = 3 or @subType = 2 then address end as address
from 
table

So the issue I have is, that if @subType is 3, then the column named ID will all be null. I then don't want to return this entire column. Conversely to that, if @subType is 2, then name would all be null so I don't want that entire column.

Answer 1

The only way you could do this would be with dynamic SQL (as Gordon mentions). Provided this is a query, and not a function, view, then you could do this:

DECLARE @subType tinyint = 3;

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT ' +
           STUFF(CASE WHEN @subType IN (1,2) THEN N',' + NCHAR(13) + NCHAR(10) + N'       id' ELSE N'' END + 
                 CASE WHEN @subType = 3 THEN N',' + NCHAR(13) + NCHAR(10) + N'       [name]' ELSE N'' END + 
                 CASE WHEN @subType IN (3,2) THEN N',' + NCHAR(13) + NCHAR(10) + N'       [address]' ELSE N'' END, 1, 10,N'') + NCHAR(13) + NCHAR(10) +
           N'FROM YourTable;';

PRINT @SQL; --Your debugging best friend.
--EXEC sp_executesql @SQL; --Uncomment to run the statement

But, if the query is coming from a presentation layer, then really that should be handling what columns are being displayed, not SQL Server

If you're passing parameters to the WHERE of your query as well, ensure that you parametrise the call to sp_executesql; do not inject the parameter values into the dynamic statement.