Results 1 to 2 of 2

Thread: How 2 use cursor in the function

  1. #1
    Join Date
    Dec 2013
    Posts
    2

    How 2 use cursor in the function

    This is ma coding! wen i execute it pops up with a error
    Msg 178, Level 15, State 1, Procedure sss, Line 27
    A RETURN statement with a return value cannot be used in this context.


    Plz HELP???!!!!

    alter function dbo.sss( @mdvn int,@fdate date,@tdate date)
    RETURNS @Tab TABLE
    (
    ifa int,
    bp int,
    hb int
    )
    AS
    BEGIN
    declare @ifa int,@bp int,@hb int;
    declare pcurs CURSOR FOR
    select @bp,@ifa,@hb from
    (
    select dvn_cd,phc_cd,hsc_cd,
    case when IFA=100 and ANEDD between @fdate and @tdate then 1 else 0 end as ifa,
    case when BP>='140/90' and ANEDD between @fdate and @tdate then 1 else 0 end as bp,
    case when HB<11 and ANEDD between @fdate and @tdate then 1 else 0 end as hb
    from ANVisits3 a where DVN_CD=@mdvn and ANEDD between @fdate and @tdate and ifa=@ifa and bp=@bp and hb=@hb
    )a group by dvn_cd,phc_cd,hsc_cd
    OPEN pcurs
    BEGIN
    fetch NEXT FROM pcurs INTO @ifa,@bp,@hb
    WHILE @@Fetch_status = 0
    BEGIN
    select 'DVN:'+ convert(varchar(20),@mdvn)+ 'IFA:'+@ifa+ 'BP:' +@bp+ 'HB:' +@hb
    fetch NEXT FROM pcurs INTO @ifa,@bp,@hb
    Return @Tab
    END
    END
    END
    --close pcurs
    --deallocate pcurs

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You are trying to return a table for each loop in the cursor. That is not allowed. You can return a select statement or a table type variable. Check books online for example.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •