Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    24

    Question Unanswered: Function Returning error during compilation.....

    Hi ,

    I am creating a function which is going to return a table. The Code ofr the function is as follows...
    ===============================
    Create function udf_qcard (@cg1 varchar(25)) returns @rec_card table (t_cusip varchar(10),t_data varchar(70))
    AS
    begin
    declare @t1_sys char(10),@t1_all varchar(11)
    declare @temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS)
    if (substring(@cg1,1,2)='Q$')
    set @cg1 = (select substring(@cg1,3,len(@cg1)) where substring(@cg1,1,2)='Q$')
    DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
    where groups_system = @cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
    insert into @temp_qcard values(@cg1)
    OPEN C1
    FETCH NEXT FROM c1 INTO @t1_sys,@t1_all
    WHILE @@FETCH_STATUS = 0
    BEGIN

    insert into @temp_qcard values(@t1_all)

    declare @t2_sys char(10),@t2_all varchar(10)
    DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
    where groups_system = @t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

    begin
    OPEN C2
    FETCH NEXT FROM c2 INTO @t2_sys,@t2_all
    WHILE @@FETCH_STATUS = 0
    BEGIN
    insert into @temp_qcard values(@t2_all)

    declare @t3_sys char(10),@t3_all varchar(10)
    DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
    where groups_system = @t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

    begin

    OPEN C3
    FETCH NEXT FROM c3 INTO @t3_sys,@t3_all
    WHILE @@FETCH_STATUS = 0
    BEGIN
    insert into @temp_qcard values(@t3_all)
    FETCH NEXT FROM c3 INTO @t3_sys,@t3_all
    end
    end
    close c3
    deallocate c3
    FETCH NEXT FROM c2 INTO @t2_sys,@t2_all
    end
    end
    close c2
    DEALLOCATE c2

    FETCH NEXT FROM c1 INTO @t1_sys,@t1_all
    END

    CLOSE c1
    DEALLOCATE c1
    Insert @rec_card select groups_q+groups_cusip,groups_data from tbl_groups
    where groups_system in (select tdata from @temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata

    RETURN
    END
    ==========================

    While compiling this I am getting the Below error ....
    ==================
    Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10
    Mixing old and new syntax to specify cursor options is not allowed.
    Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23
    Mixing old and new syntax to specify cursor options is not allowed.
    Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35
    Mixing old and new syntax to specify cursor options is not allowed.
    =================

    Can Anyone please help me how to resolve this issue...

    Thanks with Regards.

    -Mohit.

  2. #2
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39

    Re: Function Returning error during compilation.....

    Try changing the declaration of all your cursors like this

    DECLARE c1 SCROLL CURSOR READ_ONLY FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
    where groups_system = @cg1 and groups_alldata like 'Q$%' and groups_seq>=1

    Dont write "FOR READ_ONLY" at the end of the DECLARE. Instead, write it before the "FOR select....."

Posting Permissions

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