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