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,
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'
ELSE IF @Role = 'Admin'