February 22, 2007 at 10:25 am
Hi,
I need you help regarding function in t-sql. I'm using this syntax and im encountering some problems.
CREATE FUNCTION dbo.abc
(
@a int
)
RETURNS @G table(fld1 varchar(9), fld2 varchar(9),
fld3 varchar(100),fld4 int,
fld5 numeric(8),fld6 char(1),fld7 int)
AS BEGIN
INSERT into @G
select fld1 , fld2 ,
fld3 ,fld4 ,
fld5 ,fld6 ,fld7
from table1 where fld5= @a
select
case when fld7 in('cde')
then convert(varchar,convert(int,abs(max(fld2))*100),8)
when fld3= '0' and fld4= 'S' and fld5> 0 and fld1= max(fld1)
then convert(varchar,convert(int,abs(max(fld1))*100),8)
when fld3= '0' and fld5= '0' and fld4= 'S' and fld2= max(fld2)
then convert(varchar,convert(int,abs(max(fld2))*100),8)
end
from @G
the error is:
Server: Msg 444, Level 16, State 2, Procedure dbo.abc, Line 18
Select statements included within a function cannot return data to a client.
February 22, 2007 at 1:25 pm
The error is because of the second query, if you want to make changes to the data, you can update the table @G.
Also, you're missing a return at the end of the function.
CREATE FUNCTION dbo.abc
(
@a int
)
RETURNS @G table(fld1 varchar(9), fld2 varchar(9),
fld3 varchar(100),fld4 int,
fld5 numeric(8),fld6 char(1),fld7 int)
AS BEGIN
INSERT into @G
select fld1 , fld2 ,
fld3 ,fld4 ,
fld5 ,fld6 ,fld7
from table1 where fld5= @a
return
end
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply