Conditional column display

Question

Post reply

Conditional column display

nat_s

Old Hand

Points: 306
More actions
March 17, 2016 at 8:01 am

#324965

Hello all,
I've run into an issue in a stored proc. Within the Select statement, I need to conditionally display several columns based on an input param. These values also needs to be returned in a particular order, so they're in the middle of the select statement.
The first code sample worked fine when @ShowNames = 1 (it returned the names), but it returns null in each column when @ShowNames = 0. I don't want anything returned when @ShowNames = 0.
Select personId,
CASE WHEN @ShowNames = 1 THEN FirstName end,
CASE WHEN @ShowNames = 1 Then LastName end,
Address, City, ...
Then I tried using a conditional but it doesn't compile:
Select personId,
IF @ShowNames = 1
BEGIN
FirstName,
END
Address, City, ...
Could someone please help?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 77014 More actions · Answer 1

This type of formatting is usually best left to the presentation layer, SSRS for example.

The only way to do it in T-SQL is with dynamic SQL.

If you tell us what your ultimate goal is, we can better aid in finding the best approach.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

Dynamic SQL is not necessarily required – "long-winded SQL" can avoid it. For example:

DECLARE @Col varchar(10);

SET @Col = 'Col1';

--set @Col = 'Col2';

IF Object_Id('tempdb..#Test','U') IS NOT NULL

DROP TABLE #Test;

CREATE TABLE #Test ( Col1 varchar(10)

, Col2 varchar(10) );

IF @Col = 'Col1'

SELECT Col1

FROM #Test;

IF @Col = 'Col2'

SELECT Col2

FROM #Test;

But I also agree that this is something best taken care of upstream in the presentation layer.

--Edit: maybe that should have been 'downstream' 🙂

nat_s Old Hand Points: 306 More actions · Answer 3

This is an ASP.NET MVC C# app that uses NPOI to generate reports from the SQL Server database. The proc gets the column headers and values for an Excel report. Depending on user permissions, they either only see the id, or the id plus the names. If it's ID-only, then we don't want to see the empty Name columns.

It's inherited code that I don't have time to rewrite.

nat_s Old Hand Points: 306 More actions · Answer 4

I managed to fix it within the NPOI code that creates the Excel cells. Thanks for all your help.