adding conditional statement inside UPDATE

The query:

UPDATE empPac
    SET quantityLimit = allocation,
        allocationStart = '"&allocationStart&"',
        nextUpdate = DATEADD(mm, allocationMonths, "&allocationStart&"),
        lastUpdate = GETDATE(),
        quantityIssued = 0,
        quantityShipped = 0
    WHERE allocation IS NOT NULL AND
          allocationMonths <> 0 AND
          (nextUpdate <= DATEADD(mm, "&checkCondition&", GETDATE()) OR
           nextUpdate IS NULL) AND
          empIdent in (select empIdent
                       from employee
                       where custIdent='"&custIdent&"')

What I want to do is 开发者_JAVA百科add a conditional statement to the SET quantityLimit = allocation so that rather than having the WHERE allocation IS NOT NULL, I want it to have a conditional statement such as SET quantityLimit = ((allocation IS NULL) ? 0 : allocation)

You can use ISNULL():

SET quantityLimit = ISNULL(allocation, 0)

Equivalent functions for other databases are NVL() for Oracle and IFNULL() for MySQL and SQLite

What you really should be using though is COALESCE() if you want to increase the portability of your code. COALESCE is part of the SQL-92 standard and widely supported across RDBMSes.

What database do you use? For example, in oracle sql you can write case when allocation is null then 0 else allocation end or nvl (allocation, 0) or coalesce (allocation, 0)

And case syntax in MSSQL is the same as in Oracle.

This is the TSQL (MSSQL) way:

SET quantityLimit = isnull(allocation,0)

Alternatives...

SET quantityLimit = CASE WHEN allocation is null THEN 0 ELSE allocation END
--This one might be handy if you wanted to check for more than just null values.  Such as:
----...CASE WHEN allocation is null THEN 0 WHEN some_other_value THEN 1 WHEN ... THEN ... ELSE allocation END

SET quantityLimit = coalesce(allocation,0)
--This one gives you the first non-null value it finds, given a list of places to look.  Such as:
----...coalesce(allocation,some_other_field,some_nuther_field,...,0)