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.
May 11, 2021 at 8:46 am
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
May 11, 2021 at 2:42 pm
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.
May 11, 2021 at 2:48 pm
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".
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy