Check if field is null or empty and insert sql server 2008

Question

Hi All Bit rusty in sql

I have a situation where I need to insert a field "@Amount" into a temp table.If @amount from tableA is null or 0 get it from tableB

This is a simplified example of what I am doing.This must be done within a select statement when inserting into #CustomerTable Do I need a case when statement?

DECLARE @Amount DECIMAL(18,4) 

SELECT @Amount=Amount
FROM TableA

INSERT #CustomerTable(id,Name,Amount)
SELECT 1,CustomerName,--if Amount is null or 0 get it from TableB else Get it from Table A.
FROM TableB

Answer 1

INSERT #CustomerTable(id,Name,Amount)
SELECT 1,
    CASE
      WHEN Amount IS NULL or Amount = 0 THEN TableA.CustomerName
      ELSE TableB.CustomerName
    END,
    Amount
FROM TableA, TableB
-- need a WHERE clause here to get TableA/TableB records, and you need to make
-- sure you join them properly

Answer 2

Pretty close to @dcp answer, instead using a sub query in the case statement.

INSERT @CustomTable
(
    id,
    Name,
    Amount
)
SELECT 
    1, 
    CustomerName,
    CASE 
        WHEN ISNULL(TableB.Amount,0) > 0 THEN TableB.Amount
        ELSE (SELECT TableA.Amount FROM TableA WHERE 1 = 1) --Replace logic to get value from TableA
    END AS Amount
FROM TableB

Answer 3

Since you're using 2008 I'd twist the new NULLIF() function with the ISNULL() function and use a subquery:

insert @CustomTable (id, name, amount)
select
    1,
    CustomerName,
    ISNULL(NULLIF(TableA.Amount,0),(select Amount from TableB where TableB.ID = TableA.ID))
from
    TableA