如何使用if返回表的UDF？ [英] How to use if exists in a UDF that returns a table?

问题描述

大家好。



我的查询执行正常：

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')

我试图将它变成一个返回表格的UDF，以便我可以使用之后交叉申请：

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)

);

但我得到以下呃ror：

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 ')'.

我尝试过：



谷歌搜索，

我发现了这个：

如果没有找到结果，SQL查询返回字段中的值。 [ ^ ]



和许多喜欢它，但不能找不到如何将它合并到UDF，或者我缺少什么

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

推荐答案

你不能使用if，因为UDF的模式定义不是必然是独特的但您可以执行以下操作：

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

主要区别在于

- 我正在命名和定义将要使用的表格返回

- 我将Select（s）的结果插入到返回表中，而不仅仅是运行选择

- 我必须使用Begin-End对而不是括号（）

- 注意返回

这篇关于如何使用if返回表的UDF？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！