May 12, 2009 at 9:14 am
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
May 12, 2009 at 9:20 am
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
May 12, 2009 at 9:20 am
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
May 12, 2009 at 9:43 am
tks!
Command(s) completed successfully.
November 15, 2012 at 12:50 am
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.
November 15, 2012 at 7:35 pm
Hi,guys.I am new
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy