-1

I have an equation that multiplies loads of variables together, if one of those variables is 0 then I don't want it included in the equation by substituting it for 1 which won't affect the result.

A case when - then, statement for each variable validating if they're greater than 0is a bit clunky.

Is there a similar function like IsNull where if the variable is 0 then it returns an alternate value?

--edit @Backs answer is right but apparently after sql 2012 iif was taken out, when i try to write the statement there is a syntax error at the '=' sign. Is there a replacement for iif after sql-2012?

1 Answers1

2
IIF(@variable = 0, 1, @variable)
Backs
  • 24,430
  • 5
  • 58
  • 85
  • `IIF` Is syntactic sugar for a `CASE` expression, which OP doesn't want (though their reasoning seems off) – HoneyBadger Jan 24 '20 at 09:25
  • so this should work but apparently iif was taken out after sql 2012. Yeah @HoneyBadger is right, i was looking for the most syntactically sugary way of doing it –  Jan 24 '20 at 09:41
  • `declare @t int = 1 select iif (@t = 0, 1, @t)` returns `Incorrect syntax near '='.` –  Jan 24 '20 at 09:52
  • @r_mat, it doesn't for me on sql server 2016 – HoneyBadger Jan 24 '20 at 10:12
  • @HoneyBadger my bad i meant SSMS 2012 –  Jan 24 '20 at 10:45
  • 1
    SSMS is just a GUI for sql server. What version of sql server do you have (`SELECT @@VERSION`)? – HoneyBadger Jan 24 '20 at 10:47
  • [here](https://www.w3schools.com/sql/func_sqlserver_iif.asp) shows that `iif()` is available from SQL server 2012 – Gospodin Tanev Jan 24 '20 at 11:00
  • whoops i am operating on bad information from a friend which i didnt fact check, it was meant to be [before](https://stackoverflow.com/questions/11540753/sql-server-2008-iif-statement-does-not-seem-enabled) 2012 on sql - i am working on 2008 –  Jan 24 '20 at 11:09
  • @r_mat, Then simply use a `CASE` expression. Personally, I find it much easier to read anyway. – HoneyBadger Jan 24 '20 at 11:12