How can I use IF statement in SQL Server in order to show or hide some specific columns

Question

Suppose I have a table like :

(0 means in cash and any other value indicates check serial number)

ID    NAME     PaymentMethod       Amount          Bank
------------------------------------------------------------
1     Alex          0               20             ''
2     Sara       123456789         5000      Bank of America
3     Mina          0               15             ''
4     Scott      987456321        10000         Citibank

How can I use an IF statement so that if the PaymentMethod column contains 0 I hide this column and instead show a column with value of Incash and hide the bank column as well.

And when the PaymentMethod is anything else I just rename the column header to CheckSerial.

To make it more clear I want the table to look like this; for those who have paid InCash, it shows up as:

ID    NAME       PaymentMethod       Amount  
-------------------------------------------
1     Alex          InCash            20  
3     Mina          InCash            15

for those how have paid by checks, it shows up as :

ID    NAME   PaymentMethod  Serial Number     Amount          Bank
-------------------------------------------------------------------
2     Sara      Check        123456789        5000      Bank of America
4     Scott     Check        987456321        10000         Citibank

I tried something like this , based on the given IDs,only one select statement executes:

    ALTER FUNCTION dbo.Function1(@IDTag bigint)
    RETURNS TABLE
    AS 
    if PaymentMethod = 0
    BEGIN  
    RETURN SELECT         ID, Name , PaymentMethod = InCash , Amount 
           FROM            tblTest
           WHERE        (ID = @IDTag)
           END
    ELSE 
           BEGIN 
           RETURN   SELECT        ID, Name , PaymentMethod ='By Check', PaymentMethod As [Serial number], Amount, Bank 
           FROM            tblTest
           WHERE        (ID = @IDTag)

           END

Is this even correct? If this is not feasible or logical what better way can you suggest on this?

If you wanted to return different columns, then you would have to use two separate select statements. You cannot hide columns in a single select. — Taryn, Feb 20 '13 at 12:13
You won't be able to use a function to do that. Use a stored procedure. Why don't you try it? — Phil, Feb 20 '13 at 13:19
@Phil:Is there any difference? they both act the same way?Am i wrong? — Hossein, Feb 20 '13 at 13:23
@Hossein -- You are wrong -- Stored Procedures and Functions don't work at all the same in SQL server. — Hogan, Feb 20 '13 at 13:26
If you try your code in SSMS you'll find it won't work. An inline table valued function has to start with return select ... or similar. A multiline table valued function needs to define the table it will return upfront. http://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function — Phil, Feb 20 '13 at 13:27

Phil · Answer 1 · 2013-02-20T13:28:39.033

4

You can use the CASE statement to show 'In cash' or 'check' as follows.

select ID, NAME, 
    CASE PaymentMethod WHEN 0 THEN 'In cash' ELSE 'Check' END as PaymentMethod,
    Amount, Bank
from
    [mytable]

But you can't hide columns in a single result set, you would need to run two queries

select ID, NAME, 'In cash' PaymentMethod, Amount
from [mytable] where PaymentMethod = 0

and

select ID, NAME, 'Check' PaymentMethod, SerialNumber, Amount, Bank
from [mytable] where PaymentMethod <> 0

edited Feb 20 '13 at 13:28

answered Feb 20 '13 at 12:08

Phil

42,255
9
100
100

Thank you verymuch,I myself wrote a sql command, would you have a look at it as well? – Hossein Feb 20 '13 at 13:12

Hogan · Accepted Answer · 2013-02-20T13:21:11.790

2

You code is not a good plan. The calling program has no way to know what the data is going to look like. Much better to do something like Phil showed where you always return the same table but sometimes some columns are not used.

Also the SQL you show has a number of typos -- you don't have quotes around InCash and you have the same column name (PaymentMethod) twice in the second query.

This is probably the query you want:

SELECT ID, Name, 
    CASE WHEN PaymentMethod = 0 THEN 'In cash' ELSE 'Check' END as PayBy,
    -- NB we could just select PaymentMethod as SerialNumber here, but this is clearer.
    CASE WHEN PaymentMethod != 0 THEN PaymentMethod ELSE 0  END as SerialNumber,
    Amount, Bank
FROM [mytable]

edited Feb 20 '13 at 13:21

answered Feb 20 '13 at 13:14

Hogan

69,564
10
76
117

Thank you, but then it wont look consistent – Hossein Feb 20 '13 at 13:15
2

@Hossein -- You worry about the look in the UI, this is the back end SQL server... only you see it. – Hogan Feb 20 '13 at 13:21

How can I use IF statement in SQL Server in order to show or hide some specific columns

2 Answers2