Select Columns In Select Query Sql Server

Question

Declare @tempvar as Varchar(10)
    Set @tempvar='true'

    select dbo.InformationTable.Name,dbo.InformationTable.Address,dbo.InformationTable.FavoriteDish
    from dbo.InformationTable

I want to show dbo.InformationTable.FavoriteDish column only when @tempvar is set to true,else it will not include in select statement?How can i achieve this.Thanks.

Crono · Accepted Answer · 2014-03-07 18:31:19Z

1

Unfortunately there is no efficient way to conditionally exclude a column from a SELECT query.

You can either do it this way:

DECLARE @tempvar AS VARCHAR(10)
SET @tempvar='true'

IF @tempvar = 'true'
BEGIN
    SELECT
        dbo.InformationTable.Name,
        dbo.InformationTable.Address,
        dbo.InformationTable.FavoriteDish
    FROM
        dbo.InformationTable
END
ELSE
BEGIN
    SELECT
        dbo.InformationTable.Name,
        dbo.InformationTable.Address
    FROM
        dbo.InformationTable
END

Or, if you don't mind the column descending anyway as part of the resultset (with NULL values), like this:

SELECT
    dbo.InformationTable.Name,
    dbo.InformationTable.Address,
    CASE
        WHEN @tempvar = 'true' THEN
            dbo.InformationTable.FavoriteDish
        ELSE NULL
    END FavoriteDish
FROM
    dbo.InformationTable

You also could build your query dynamically in a VARCHAR variable and pass it to the EXEC method:

DECLARE @myquery VARCHAR(1000)

/*
Build your query here on your own conditions
*/

EXEC(@myquery)

I strongly advise against the last one, though, because it comes with multiple tradeoffs.

Hope that helps.

edited Mar 7, 2014 at 18:31

answered Mar 7, 2014 at 17:56

Crono

10.5k6 gold badges47 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Muhammad Ali Over a year ago

Thanks for your reply .Is there is no cool way instead of replicate the code?

Crono Over a year ago

The cool way might be using the third option I gave you, but as I said this is something I don't recommend. In T-SQL you must favor the efficient way over the cool way. Always.

Crono Over a year ago

IMHO you should ask yourself why you want to exclude the column at server level instead of having the client make the right query.

Collectives™ on Stack Overflow

Select Columns In Select Query Sql Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related