Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select statements included within a function cannot return data to a client Expand / Collapse
Author
Message
Posted Tuesday, May 12, 2009 9:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 06, 2013 5:50 AM
Points: 9, Visits: 25
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
Post #715199
Posted Tuesday, May 12, 2009 9:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,556, Visits: 2,593
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

Post #715209
Posted Tuesday, May 12, 2009 9:20 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, September 19, 2013 6:00 AM
Points: 715, Visits: 2,706
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
Post #715210
Posted Tuesday, May 12, 2009 9:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 06, 2013 5:50 AM
Points: 9, Visits: 25
tks!

Command(s) completed successfully.
Post #715234
Posted Thursday, November 15, 2012 12:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 22, 2014 4:43 PM
Points: 8, Visits: 50
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.


Post #1384995
Posted Thursday, November 15, 2012 7:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 7:33 PM
Points: 9, Visits: 0
Hi,guys.I am new




Post #1385419
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse