SELECT column based on parameter

  • 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.

     

  • 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
  • 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.

     

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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