创建T-SQL表值函数错误:[附近有语法错误..]

Question

我正在尝试创建一个表值函数，该函数将执行以下操作：根据一个参数的值，在不同的表上[选择*]。

在下面的代码中，我得到了错误：[OutputTable]附近的语法不正确，我不知道为什么。我曾尝试在INSERT语句中显式列出@OutputTable的字段，但仍然出现了同样的错误。NB：我在Synapse。

CREATE FUNCTION ufn_get_pipeline_contracts (@ContractDimensionName [varchar](max))
RETURNS 
@OutputTable TABLE
(
[ContractID] [varchar](255) NULL,
[CompanyID] [varchar](15) NULL,
[ContractName] [varchar](100) NULL,
[CompanyName] [varchar](100) NULL

)
AS 
BEGIN
IF (@ContractDimensionName = 'DimContractType1')
BEGIN
Insert Into @OutputTable 
Select
ContractID, CompanyID, ContractName, CompanyName
From DimContractType1
END
ELSE IF (@ContractDimensionName = 'DimContractType2')
BEGIN
Insert Into @OutputTable 
Select 
ContractID, CompanyID, ContractName, CompanyName
From DimContractType2
END

ELSE IF (@ContractDimensionName = 'DimContractType3')
BEGIN
Insert Into @OutputTable 
Select 
ContractID, CompanyID, ContractName, CompanyName
From DimContractType3
END

RETURN
END

Answer 1

您可以稍微简化

CREATE FUNCTION ufn_get_pipeline_contracts (@ContractDimensionName varchar(max))
RETURNS 
Table
AS 
Return (
Select ContractID, CompanyID, ContractName, CompanyName From DimContractType1 Where @ContractDimensionName = 'DimContractType1'
Union All
Select ContractID, CompanyID, ContractName, CompanyName From DimContractType2 Where @ContractDimensionName = 'DimContractType2'
Union All
Select ContractID, CompanyID, ContractName, CompanyName From DimContractType3 Where @ContractDimensionName = 'DimContractType3'
)