CODEWITHSUNDEEP
sqlt-sql
PJD
PJD

Reputation: 775

Select Statements based on Variable

I am sure this should be something quite simple but I cant work out the syntax.

I have a variable called domain this can be either populated or not, when it is populated I want to run one statement but when its not I want to run a different one. I would say it should be something like in a very simplistic form.

IF @Domain = '.co.uk'

select * from table where domain = @domain

else

select * from table

end

Not sure if that is enough to give someone something to start with.

Thanks P

Thought it might be better to post the exact code I am using as this may alter some of the responses and make things a bit easier to explain.

The code shown below is the exact one I will be running and to be fair the case based on the variable might be easier on the join rather than 2 select statements.

select affiliate_id
      ,campaign_id
      ,count(*) as leads
from [BDatabase]..affil a (nolock)
where cast(created_datetime as date) between @startdate and @enddate
    and (a.domain = @domain
        or isnull(@Domain,'') = ''
        )
group by affiliate_id
        ,campaign_id

Upvotes: 0

Views: 51

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28781

SELECT * FROM tableName
WHERE domain = CASE WHEN ISNULL(@Domain,'') = '' THEN domain ELSE @Domain END

As per your your edited query pasted you can use above solution as below

select affiliate_id
      ,campaign_id
      ,count(*) as leads
from [BDatabase]..affil a (nolock)
where cast(created_datetime as date) between @startdate and @enddate
    and a.domain = CASE WHEN ISNULL(@domain,'') = '' THEN a.domain ELSE @domain END              
group by affiliate_id
        ,campaign_id

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

select * from table 
where (domain = @domain and @Domain = '.co.uk') 
or (@domain is null or @domain='')

Upvotes: 0

Related Questions