一分为二，让返回table的Function使用逻辑判断

最新推荐文章于 2022-07-27 23:17:23 发布

etzrp

最新推荐文章于 2022-07-27 23:17:23 发布

阅读量1.8k

点赞数

本文链接：https://blog.csdn.net/ETZRP/article/details/6778894

版权

数据库应用与开发专栏收录该内容

12 篇文章 0 订阅

订阅专栏

在SQL Server 2005中，函数分为三种：返回单值函数，返回内联table函数，返回多语句table函数，在返回table的函数中是不能使用逻辑的判断和其他语句的，只能使用查询语句。如果遇到这种情况，应该把需要进行逻辑判断的部分封装到另外的返回单值的函数里。

比如如下的代码是不能运行的：

Create Function [dbo].[F_BindedSuppliers](@SupplierID int)
RETURNS TABLE
as return
(
declare @MainSupplierID int

if exists(select 1 from dbo.SupplierBinder where MainSupplierID=@SupplierID)
begin
	set @MainSupplierID=@SupplierID
end

else if exists(select 1 from dbo.SupplierBinder where SubSupplierID=@SupplierID)
	select @MainSupplierID=MainSupplierID from dbo.SupplierBinder where SubSupplierID=@SupplierID

if @MainSupplierID is null
	select @SupplierID as SupplierID
else
	select @MainSupplierID as SupplierID
		union
	select SubSupplierID from SupplierBinder where MainSupplierID= @MainSupplierID
)

这样将会收到语法错误的报告。进行逻辑函数拆分后，代码如下：

go

CREATE FUNCTION [dbo].[GetMainSupplieID]
(@SupplierID int)
RETURNS int
AS
BEGIN
declare @MainSupplierID int

if exists(select 1 from dbo.SupplierBinder where MainSupplierID=@SupplierID)
begin
	set @MainSupplierID=@SupplierID
end

else if exists(select 1 from dbo.SupplierBinder where SubSupplierID=@SupplierID)
	select @MainSupplierID=MainSupplierID from dbo.SupplierBinder where SubSupplierID=@SupplierID
if @MainSupplierID is null
	set  @MainSupplierID=@SupplierID

	return @MainSupplierID
END

go

Create Function [dbo].[F_BindedSuppliers](@SupplierID int)
RETURNS TABLE
as return
(
	select [dbo].[GetMainSupplieID](@SupplierID) as SupplierID
		union
	select SubSupplierID from SupplierBinder where MainSupplierID= [dbo].[GetMainSupplieID](@SupplierID)
)

go

这样就可以顺利实现预想功能了。