SELECT 
@BookQtyIN = ISNULL(SUM(x.pickqty), 0),
@PickQtyIN = CASE WHEN ISNULL(SUM(x.pickqty), 0) >0 THEN
	       ISNULL(SUM(d.PickQty), 0) - ISNULL(SUM(x.pickqty), 0) 
             ELSE ISNULL(SUM(d.PickQty), 0)END,

FROM	tblItemDetail d

LEFT JOIN (SELECT p.PickQty AS PickQty, 
                  j.PickComplete AS PickComplete, 
                  p.ItemStorageID AS ItemStorageID 
           FROM tblItempicked p
	   LEFT JOIn tblJobDocuments j 
           ON j.Docrefid=p.Docrefid AND j.JobID=p.JobID
	   WHERE j.PickComplete='N'
	   )AS x ON x.ItemStorageID=d.ItemStorageID

WHERE d.ItemID = @ItemID
      AND d.Ownership = @ClientID
      AND d.CustomLotNo = @CustomLotNo

declare @t table
(
	col int
)

delete @t
insert into @t select 1 union all select 2

select s = sum(col) from @t
/*
s           
----------- 
3
*/

delete @t
insert into @t select 1 union all select NULL

select s = sum(col) from @t
/*
s           
----------- 
1

Warning: Null value is eliminated by an aggregate or other SET operation.
*/

delete @t
insert into @t select NULL union all select NULL

select s = sum(col) from @t
/*
s           
----------- 
NULL


Warning: Null value is eliminated by an aggregate or other SET operation.
*/