CODEWITHSUNDEEP
sql-server
BladeHal
BladeHal

Reputation: 783

SQL IF else select clause

I need to be able to do an IF ELSE in the having clause of a select statement. Here is an excerpt of what I am trying to do:

HAVING  
IF      @Vendor IS NOT NULL 
THEN    (COUNT(Iron_1.SN) > 0) AND [User].UserIdentifier = @Vendor AND Location_1.LocationName = @LocationName
ELSE

(COUNT(Iron_1.SN) > 0) AND Location_1.LocationName = @LocationName

I want certain filters based on if the user provided a value for a query parameter or not.

Upvotes: 0

Views: 122

Answers (1)

Jason
Jason

Reputation: 3960

Try this instead

HAVING COUNT(Iron_1.SN) > 0 AND Location_1.LocationName = @LocationName AND
        (@Vendor IS NULL OR (@Vendor IS NOT NULL AND [User].UserIdentifier = @Vendor))

or this slightly simplier version

HAVING COUNT(Iron_1.SN) > 0 AND Location_1.LocationName = @LocationName AND
        (@Vendor IS NULL OR [User].UserIdentifier = @Vendor)

try the two and go for the one you like the best and works

Upvotes: 3

Related Questions