Select statements included within a function cannot return data to a client

  • Hi,

    i'm trying create on function, but the sql print:

    Msg 444, Level 16, State 2, Procedure Retorna_UltimoLog, Line 9

    Select statements included within a function cannot return data to a client.

    somepeople,

    can help me?

    the query is

    create function Retorna_UltimoLog (@codPromocao int)

    RETURNS varchar(200)

    as

    BEGIN

    declare @Descricao as varchar(200)

    select top 1 lt.txtDescricao

    from gpv_promocoes p

    inner join GPV_Log l

    on p.codPromocao = l.cod_objeto

    inner join GPV_Log_Tipo lt

    on lt.cod_tipolog = l.cod_tipolog

    where p.codPromocao = @codPromocao and lt.txtDescricao = @Descricao

    order by l.cod_Log DESC

    Return @Descricao

    END

  • I suspect that the query is logically incorrect. Since you have declared the variable @Descricao but not assigned any value to it.

    declare @Descricao as varchar(200)

    select top 1 lt.txtDescricao

    from gpv_promocoes p

    inner join GPV_Log l

    on p.codPromocao = l.cod_objeto

    inner join GPV_Log_Tipo lt

    on lt.cod_tipolog = l.cod_tipolog

    where p.codPromocao = @codPromocao and lt.txtDescricao = @Descricao

    --Ramesh


  • You need to assign the returning value from the select to the variable you declared.

    Like that :

    DECLARE @var nvarchar(200)

    select @var = top 1 field1 from table1

    return @Var.

    That will fix your problem, but I think you should use an Inline function for that, just do this :

    create function fn1

    @Param nvarchar(100)

    returns nvarchar(200)

    as

    BEGIN

    Return (Select top 1 field1 from table1 where Field2 = @Param)

    END

    Hope it helps, if you need some working code, just tell me, I only put sample for you to understand.

    Cheers,

    J-F

  • tks!

    Command(s) completed successfully.

  • Thanks for the suggestion about the inline function. I am a real novice at writing functions and this quickly solved a problem that I was struggling with.

  • Hi,guys.I am new

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply