Concat in函数

Question

当我尝试从一个工作的concat select创建函数时，我遇到了一个问题

这里的函数

CREATE FUNCTION dbo.afEvrGetConsequencesConcat
(@id_evr int,@type_evr int)
RETURNS varchar(max)
AS
BEGIN

    declare @result varchar(max)
    set @result= ''

IF @type_evr = 0 
BEGIN

    SELECT @result = @result + (case when (CEV.ID_CON is null) then CEV.NOM_CEV else CON.NOM_CON end) + ' ; '  
    from T_CONSEQUENCE_EVRP CEV 
    left join T_CONSEQUENCE CON on CEV.id_con=CON.id_con
    WHERE CEV.id_evr = @id_evr

END

IF @type_evr = 1
BEGIN

        SELECT DISTINCT @result = @result + isnull(NOM_PHR,'') + ' ; '  
        FROM T_EVRP EVR
        INNER JOIN T_EVR_CHIM CHIM on EVR.ID_EVR=CHIM.ID_EVR
        INNER JOIN T_PRODUIT PROD on CHIM.ID_PROD=PROD.ID_PROD
        INNER JOIN T_PRODUIT_PHRASE_R PROD_PHR on PROD.ID_PROD=PROD_PHR.ID_PROD
        INNER JOIN T_PHRASE_R PHR on PROD_PHR.ID_PHR=PHR.ID_PHR
        WHERE TYPE_EVR=1 AND EVR.id_evr = @id_evr AND PHR.flag_consequence = 1

        UNION 

        SELECT DISTINCT @result = @result + isnull(nom_cons_txt,'') +' ; '  
        FROM T_EVRP EVR
        INNER JOIN T_EVR_CHIM CHIM on EVR.ID_EVR=CHIM.ID_EVR
        INNER JOIN T_PRODUIT PROD on CHIM.ID_PROD=PROD.ID_PROD
        INNER JOIN t_consequence_txt PROD_CONS on PROD.ID_PROD=PROD_CONS.ID_PROD
        WHERE TYPE_EVR=1 AND EVR.id_evr = @id_evr AND PROD.transverse_prod = 1

END

    RETURN @result

END
GO

消息错误是‘Msg’444，Niveau‘16，état’2，Procédure‘afEvrGetConsequencesConcat，Ligne’23包含在函数中的Select语句不能向客户端返回数据。‘

上面的SELECT在函数外部工作得很好。每次，我都会在@result中得到1个varchar，它与SELECT concat匹配。这个类似的函数也运行得很好：

CREATE FUNCTION dbo.afEvrGetControleConcat
(@id_evr int,@type_evr int,@type_ctrl int)
RETURNS varchar(8000)
AS
BEGIN
    declare @result varchar(8000)
    set @result= ''

    -- 0 tous
    -- 1 date + comm


    IF @type_ctrl = 0
    BEGIN   

        SELECT @result = @result + ( CONVERT(VARCHAR(10),date_ctr, 103) + ' - ' + CONVERT(VARCHAR(30),isnull(valeur_mesure,'')) + ' - ' + isnull(unite_mesure,'') + ' - ' + com_ctr  ) + ' ;'
        FROM t_controle_exposition 
        WHERE id_evr = @id_evr

    END

    IF @type_ctrl = 1
    BEGIN   

        SELECT @result = @result + ( CONVERT(VARCHAR(10),date_ctr, 103) + ' - ' + com_ctr  ) + ' ;'
        FROM t_controle_exposition 
        WHERE id_evr = @id_evr

    END

    RETURN @result

END
GO

那么问题出在哪里呢？按照逻辑，我想返回一个varchar，而不是一个表或任何时髦的数据。所以我不理解这个错误信息。

谢谢你的帮助。

编辑:添加一个临时表，并在UNION和concat next的结果中插入:)

CREATE FUNCTION dbo.afEvrGetConsequencesConcat
(@id_evr int,@type_evr int)
RETURNS varchar(max)
AS
BEGIN

    declare @result varchar(max)
    set @result= ''
    declare @t as table (v varchar(max))

IF @type_evr = 0 
BEGIN

    SELECT @result = @result + (case when (CEV.ID_CON is null) then CEV.NOM_CEV else CON.NOM_CON end) + ' ; '  
    from T_CONSEQUENCE_EVRP CEV 
    left join T_CONSEQUENCE CON on CEV.id_con=CON.id_con
    WHERE CEV.id_evr = @id_evr

END

IF @type_evr = 1
BEGIN

        insert into @t 
        SELECT DISTINCT isnull(NOM_PHR,'') + ' ; '  
        FROM T_EVRP EVR
        INNER JOIN T_EVR_CHIM CHIM on EVR.ID_EVR=CHIM.ID_EVR
        INNER JOIN T_PRODUIT PROD on CHIM.ID_PROD=PROD.ID_PROD
        INNER JOIN T_PRODUIT_PHRASE_R PROD_PHR on PROD.ID_PROD=PROD_PHR.ID_PROD
        INNER JOIN T_PHRASE_R PHR on PROD_PHR.ID_PHR=PHR.ID_PHR
        WHERE TYPE_EVR=1 AND EVR.id_evr = @id_evr AND PHR.flag_consequence = 1

        UNION 

        SELECT DISTINCT isnull(nom_cons_txt,'') +' ; '  
        FROM T_EVRP EVR
        INNER JOIN T_EVR_CHIM CHIM on EVR.ID_EVR=CHIM.ID_EVR
        INNER JOIN T_PRODUIT PROD on CHIM.ID_PROD=PROD.ID_PROD
        INNER JOIN t_consequence_txt PROD_CONS on PROD.ID_PROD=PROD_CONS.ID_PROD
        WHERE TYPE_EVR=1 AND EVR.id_evr = @id_evr AND PROD.transverse_prod = 1

SELECT @result = @result + v FROM @t

END

    RETURN @result

END
GO

Answer 1

我想这和工会有关。尝试运行：

DECLARE @result varchar(max)

SELECT @result = 'test'

UNION 

SELECT @result = 'another'

您将获得：

Msg 10734, Level 15, State 1, Line 5
Variable assignment is not allowed in a statement containing a top level UNION, INTERSECT or EXCEPT operator.

如果您必须使用UNION，那么您将不得不返回一个表，否则会想出一些没有UNION的其他方法。这可以解释为什么第二个函数可以工作，因为它没有UNION