CODEWITHSUNDEEP
sqlsql-server
IQtheMC
IQtheMC

Reputation: 251

SQL using case in a where clause

What I have now works:

insert GroupAcceptedPaymentType (GroupID, PaymentTypeID)
    select
        @group_id,
        pt.PaymentTypeID
    from 
        PaymentType pt
    where charindex(pt.Title, @payment_type) > 0

until @payment_type is null, then nothing gets inserted.

Can anyone help me find an elegant way to do this? (where clause is pseudocode )

insert GroupAcceptedPaymentType (GroupID, PaymentTypeID)
    select
        @group_id,
        pt.PaymentTypeID
    from 
        PaymentType pt
    where 
        if @payment_type is null
        PaymentTypeID = 2
        else
        charindex(pt.Title, @payment_type) > 0

Edit: I really appreciate the answers, but unfortunately, none of these answers update the table. This pseudocode may explain better what needs to happen. If @payment_type is null, then insert the groupID into the GroupId column, and a value of 2 into the PaymentTypeId column, else run the statement as normal. 

        where 
        if @payment_type is null
        INSERT INTO GroupAcceptedPaymentType (GroupID, PaymentTypeID)
        VALUES (GroupId, '2')
        else
        charindex(pt.Title, @payment_type) > 0

Edit: We found the solution, but it won't let me answer my own question(for 8 hours. Maybe I should set my alarm?), but here it is. Hopefully it helps someone:

if @payment_type <> ''
begin
insert GroupAcceptedPaymentType (GroupID, PaymentTypeID)
select
    @group_id,
    pt.PaymentTypeID
from 
    PaymentType pt
where charindex(pt.Title, @payment_type) > 0
end
else
begin 
INSERT INTO GroupAcceptedPaymentType (GroupID, PaymentTypeID)
    VALUES (@group_id, '2')
end

Upvotes: 0

Views: 58

Answers (3)

nrainer
nrainer

Reputation: 2623

Try this - 

where (@payment_type is null and PaymentTypeID = 2) 
or (charindex(pt.Title, @payment_type) > 0)

Upvotes: 1

Laszlo Korte
Laszlo Korte

Reputation: 1179

How about

select
    @group_id,
    pt.PaymentTypeID
from 
    PaymentType pt
where 
    (@payment_type is null AND PaymentTypeID = 2)
    OR
    (@payment_type is not null AND charindex(pt.Title, @payment_type) > 0)

Upvotes: 2

user2989408
user2989408

Reputation: 3137

You can do that using OR

insert GroupAcceptedPaymentType (GroupID, PaymentTypeID)
select
    @group_id,
    pt.PaymentTypeID
from 
    PaymentType pt
where 
    (@payment_type is null AND 
    PaymentTypeID = 2 ) 
    OR 
    (charindex(pt.Title, @payment_type) > 0)

Upvotes: 1

Related Questions