Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello everyone

I have a query that executes fine:

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 = 212 and invd.ExpireDate ='20190430' and
upper(invd.batch) = upper('vatg') and inv.InvoiceDate <= '20170101'
order by inv.InvoiceDate desc)
select top(1) invd.UnitCost from InvoiceDetails invd join Invoices inv
on invd.InvoiceID = inv.InvoiceID 
where inv.InvoiceTypeID = 3 and invd.ItemID = 212 and invd.ExpireDate = '20190430' and
upper(invd.batch) = upper('vatg') and inv.InvoiceDate <= '20170101'
order by inv.InvoiceDate desc
Else
Select UnitCost From dbo.GetLastUnitCost(212,'20190430','vatg')


I tried to make it into a UDF that returns table so that I can use it with cross apply later:

create Function [dbo].[GetLastUnitCostBeforeDate2](@ItemID int , @ExpDate date , @Batch nvarchar(20) ,@InvoiceDate datetime )
returns table
as
return
(

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)
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
Select UnitCost From dbo.GetLastUnitCost(@ItemId,@ExpDate,@Batch)

);


But I get the following error:

Msg 156, Level 15, State 1, Procedure GetLastUnitCostBeforeDate2, Line 8 [Batch Start Line 7]
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Procedure GetLastUnitCostBeforeDate2, Line 21 [Batch Start Line 7]
Incorrect syntax near ')'.


What I have tried:

Googling,
I found this:
SQL Query return value in a field if no results found..[^]

and many like it, but couldn't find how to incorporate it to a UDF, or what am I missing
Posted

1 solution

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:
SQL
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
 
Share this answer
 
Comments
Ahmad_kelany 6-Dec-18 8:11am    
Thank you very much.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900