时间戳

首页 > 解决方案 > 创建函数时出错 - 如果不使用 EXISTS 引入子查询，则选择列表中只能指定一个表达式

sql - 创建函数时出错 - 如果不使用 EXISTS 引入子查询，则选择列表中只能指定一个表达式

问题描述

我正在尝试运行我的查询但返回错误：

当不使用 EXISTS 引入子查询时，选择列表中只能指定一个表达式。

在修改查询方面需要的建议？

CREATE function [dbo].[GET_BC_SME_BO_FOR_DR_TEST](@bc_sme_bo VARCHAR(100),@activity_name VARCHAR(100),@activity_type varchar(100))
returns table 
return
(
    select 
    CASE WHEN (@activity_name ='BIA') 
    THEN 
        (
        select top 1 IIF(@bc_sme_bo='BC',bc_coordinator_id,''),IIF(@bc_sme_bo='SME',sme_id,''),IIF(@bc_sme_bo='BO',business_owner_id,'')
        from bcm_bia_department
        where bia_id in
            (
                select top 1 bcm_bia_department.bia_id 
                from bcm_bia_department 
                full join bcm_bia_department_history on bcm_bia_department.bia_id = bcm_bia_department_history.bia_id 
                where bcm_bia_department.bia_name=@activity_type or  bcm_bia_department_history.bia_name=@activity_type 
            )
        )
    WHEN (@activity_name ='BCP') 
    THEN 
        (
        select top 1 IIF(@bc_sme_bo='BC',bc_id,''),IIF(@bc_sme_bo='SME',sme,''),IIF(@bc_sme_bo='BO',business_owner_id,'')
        from bcm_bcp
        where bcp_id in
            (
                select top 1 bcm_bcp.bcp_id 
                from bcm_bcp 
                full join bcm_bcp_history on bcm_bcp.bcp_id = bcm_bcp_history.bcp_id 
                where bcm_bcp.bcp_name=@activity_type or  bcm_bcp_history.bcp_name=@activity_type 
            )
        )

    ELSE 0
    END
)

标签： sqlsql-server

解决方案

我认为你必须这样写：

CREATE function [dbo].[GET_BC_SME_BO_FOR_DR_TEST](@bc_sme_bo VARCHAR(100),@activity_name VARCHAR(100),@activity_type varchar(100))
returns table 
return
(
    SELECT field1, field2, field3 
    FROM (
        select top 1 'BIA' as activity,IIF(@bc_sme_bo='BC',bc_coordinator_id,'') as field1,IIF(@bc_sme_bo='SME',sme_id,'') as field2,IIF(@bc_sme_bo='BO',business_owner_id,'') as field3
        from bcm_bia_department
        where bia_id in
            (
                select top 1 bcm_bia_department.bia_id 
                from bcm_bia_department 
                full join bcm_bia_department_history on bcm_bia_department.bia_id = bcm_bia_department_history.bia_id 
                where bcm_bia_department.bia_name=@activity_type or  bcm_bia_department_history.bia_name=@activity_type 
            )
        UNION ALL 
        select top 1 ,'BCP' as activity,IIF(@bc_sme_bo='BC',bc_id,''),IIF(@bc_sme_bo='SME',sme,''),IIF(@bc_sme_bo='BO',business_owner_id,'')
        from bcm_bcp
        where bcp_id in
            (
                select top 1 bcm_bcp.bcp_id 
                from bcm_bcp 
                full join bcm_bcp_history on bcm_bcp.bcp_id = bcm_bcp_history.bcp_id 
                where bcm_bcp.bcp_name=@activity_type or  bcm_bcp_history.bcp_name=@activity_type 
            )
        WHERE activity = @activity_name
        ) x
    END
)

推荐阅读