You can't use if like that because the schema definition for the UDF is not necessarily unique. But you can do something like the following:
create Function [dbo].[GetLastUnitCostBeforeDate2](@ItemID int , @ExpDate date , @Batch nvarchar(20) ,@InvoiceDate datetime )
returns @result table (res decimal (15,2))
as
begin
if Exists (select top(1) invd.UnitCost
from InvoiceDetails invd
join Invoices inv on invd.InvoiceID = inv.InvoiceID
where inv.InvoiceTypeID = 3 and invd.ItemID = @ItemID
and invd.ExpireDate = @ExpDate
and upper(invd.batch) = upper(@Batch) and inv.InvoiceDate <= @invoiceDate
order by inv.InvoiceDate desc)
insert into @result
select top(1) invd.UnitCost from InvoiceDetails invd
join Invoices inv on invd.InvoiceID = inv.InvoiceID
where inv.InvoiceTypeID = 3 and invd.ItemID = @ItemID and invd.ExpireDate = @ExpDate and
upper(invd.batch) = upper(@Batch) and inv.InvoiceDate <= @invoiceDate
order by inv.InvoiceDate desc
Else
insert into @result Select UnitCost From dbo.GetLastUnitCost(@ItemId,@ExpDate,@Batch)
return;
end
The main differences being that
- I'm naming and defining the table that will be returned
- I'm inserting the results of the Select(s) into the return table not just running a select
- I have to use a Begin-End pair rather than brackets ( )
- watch out for the
return