Selecting Columns That Are Not All Null

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

Solution 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(CASEWHEN@subTypeIN (1,2) THEN N','+NCHAR(13) +NCHAR(10) + N'       id'ELSE N''END+CASEWHEN@subType=3THEN N','+NCHAR(13) +NCHAR(10) + N'       [name]'ELSE N''END+CASEWHEN@subTypeIN (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.

Solution 2:

This is too long for a comment.

A SQL query returns a fixed set of columns, with the names and types defined in the SELECT. It cannot have a variable number of columns.

In order to do what you want, you would need to use dynamic SQL. I'm not sure if that works in your context. For instance, dynamic SQL is not supported in SQL Server user-defined functions.