How to create T - SQL statement that return different result set base on criteria (IF statement)

Question

SQL, i created a SP that will return a result set base on pass criteria, i tried this statement

CREATE PROCEDURE [dbo].[GetAllMerchandise]
    @param varchar(50)
AS
BEGIN
    SELECT CASE WHEN @param = 'Regular' THEN
        SELECT id, lookup_code,item_name,regular_price 
             FROM dbo.vwMenuAndItemLookup ORDER By item_name ASC
    WHEN @param = 'Promo' THEN
        SELECT id, lookup_code,item_name,promo_price 
            FROM dbo.vwMenuAndItemLookup ORDER By item_name ASC
    WHEN @param = 'Employee' THEN
        SELECT id, lookup_code,item_name,employee_price 
            FROM dbo.vwMenuAndItemLookup ORDER By item_name ASC
    WHEN @param = 'Custom' THEN
        SELECT id, lookup_code,item_name,custom_price 
            FROM dbo.vwMenuAndItemLookup ORDER By item_name ASC
    END
END

but no luck, can you help me guys thanks in advance. Answer with examples most appreciated.

score 3 · Accepted Answer · answered Apr 19 '14 at 01:40

3

You don't want different results set, you simply want a different column...

SELECT id, lookup_code,item_name,
    CASE WHEN @param = 'Regular' THEN regular_price
         WHEN @param = 'Promo' THEN promo_price
         WHEN @param = 'Employee' THEN employee_price
         WHEN @param = 'Customer' THEN custom_price        
    END as thePrice
    FROM dbo.vwMenuAndItemLookup ORDER By item_name ASC

answered Apr 19 '14 at 01:40

Sparky

14,967
2
31
45

Was about to post the same exact solution. +1 – pinkfloydx33 Apr 19 '14 at 01:48
sorry can't vote..i'm new here..but thanks a lot @Sparky – codefrog Apr 19 '14 at 01:52
@user3550637: but you can [**accept this answer**](http://meta.stackexchange.com/q/5234/153998). This will show your appreciation for the people who *spent their own time to help you*. – marc_s Apr 19 '14 at 06:32

score 1 · Answer 2 · answered Apr 19 '14 at 01:44

The CASE WHEN ... THEN ... ELSE ... END construct only works for individual expressions, not for whole result sets. It appears you want the result set to look the same, but just vary the value of the price column - you can do that like so:

CREATE PROCEDURE [dbo].[GetAllMerchandise] @param varchar(50)
AS
BEGIN 
    SELECT id, lookup_code, item_name,
        CASE WHEN @param = 'Regular' THEN regular_price
             WHEN @param = 'Promo' THEN promo_price
             WHEN @param = 'Employee' THEN employee_price
             WHEN @param = 'Custom' THEN custom_price END AS price
    FROM dbo.vwMenuAndItemLookup
    ORDER By item_name ASC
END

How to create T - SQL statement that return different result set base on criteria (IF statement)

2 Answers2