SELECT column based on parameter

Question

SELECT column based on parameter

SSCertifiable

Points: 5905

May 11, 2021 at 12:36 am

Is there a way in the same SELECT statement to show different columns without using dynamic SQL? I have some columns I don't want to show if the user is not admin. For example, I don't want to show the "Pay" column if the user asking is not an admin. There are other columns I would like to show or hide based on parameters passed.

Here is an example where I use an if statement but I don't want multiple select statements for each parameter difference. We can filter rows in the "Where" and "Join" clauses. Is there a way to do a similar thing with columns?

DECLARE @Employees TABLE
(
ID smallint PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL,
Status varchar(10),
Pay decimal(10,2),
JobType varchar(30) NULL
)

INSERT @Employees VALUES (1,'Jeff','Bagwell', 'Active', 250.75, 'Manager')
INSERT @Employees VALUES (2,'Jose','Lima', 'Active', 175.50, 'Designer')
INSERT @Employees VALUES (3,'Chris','Truby', 'Terminated', 220.00, 'Engineer')
INSERT @Employees VALUES (4,'Craig','Biggio', 'Active', 220, 'Engineer')

DECLARE @Role varchar(20)
SELECT @Role = 'User'
--SELECT @Role = 'Admin'

IF @Role = 'User'
BEGIN
SELECT FirstName,
   LastName,
   Status,
   JobType
FROM @Employees
END
ELSE IF @Role = 'Admin'
BEGIN
SELECT FirstName,
   LastName,
   Status,
   Pay,
   JobType
FROM @Employees
END

Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 1

DECLARE @Employees TABLE
(
ID smallint PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL,
Status varchar(10),
Pay decimal(10,2),
JobType varchar(30) NULL
)

INSERT @Employees VALUES (1,'Jeff','Bagwell', 'Active', 250.75, 'Manager')
INSERT @Employees VALUES (2,'Jose','Lima', 'Active', 175.50, 'Designer')
INSERT @Employees VALUES (3,'Chris','Truby', 'Terminated', 220.00, 'Engineer')
INSERT @Employees VALUES (4,'Craig','Biggio', 'Active', 220, 'Engineer')

DECLARE @Role varchar(20)
SELECT @Role = 'User'
--SELECT @Role = 'Admin'

SELECT FirstName, LastName, Status, IIF(@Role = 'Admin',Pay,NULL) as Pay, JobType
FROM @Employees

SET @Role = 'Admin'

SELECT FirstName, LastName, Status, IIF(@Role = 'Admin',Pay,NULL) as Pay, JobType
FROM @Employees

tshad SSCertifiable Points: 5905 More actions · Answer 2

That would be the same as using a case statement but it leaves you with an empty (null) column.

I was trying to not have the column at all, if possible. So that the "User" option would have no column for Pay. There could be 4 or 5 options That I would have where I would like to have a different number of columns based on the parameters.

Thanks for the sample, though. I didn't know about the IIF function.

ScottPletcher SSC Guru Points: 101097 More actions · Answer 3

tshad wrote:

Is there a way in the same SELECT statement to show different columns without using dynamic SQL?

No.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".