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.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...."
Friday, March 9, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment