Solved

DECLARE cursorname CURSOR FOR CALL storedprocedure(a,b)

Posted on 2003-12-12
15
2,700 Views
Last Modified: 2012-05-04
I CANT MAKE IT WORK! im new to this SP on sybase thing and i cant even distinguish if im using watcom, ESQL,SP, OR T-SQL! grr...
been trying this for 8hrs already and i cant figure out why.

I NEED EXAMPLES. I cant find any.

give me an "working" example of
DECLARE cursorname CURSOR FOR CALL storedprocedure(a,b);
open cursorname;
FETCH next
close cursorname;

I need to call the storeprocedure that returns the result set.

Anyone care to give an example?
ASA.
Will give the A 500pts to the first working example.
0
Comment
Question by:Jerry_Pang
  • 8
  • 7
15 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9931825
Jerry this is how you will do this.

Say your proc is ,
Create Proc sp_MyProc
As
Select Col1, Col2
from MyTable

Change it so that result set is inserted into a temp table
Create Proc sp_MyProc
As
Begin
Create Table #Temp
(col1 int,
 col2 int
)

Insert into #Temp (Col1, Col2)
Select Col1, Col2
from MyTable

End
Now you can access the #Temp from parent proc


-- Create Temp table outside the parenr proc
Create Table #Temp
(col1 int,
 col2 int
)
go



Create Procedure sp_Parent
AS
Begin

exec sp_MyProc

select * from #Temp

--Or you can do this
Declare MyCursor Cursor For Select col1, col2 from #Temp

End

go

exec sp_Parent
go

HTH








0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9931836
0
 
LVL 9

Author Comment

by:Jerry_Pang
ID: 9932509
accoring here, i can use call procedude. i want to use this

DECLARE CURSOR statement [ESQL] [SP]

--------------------------------------------------------------------------------

 Description
Use this statement to declare a cursor. Cursors are the primary means for manipulating the results of queries.

 Syntax 1 [ESQL]
DECLARE cursor-name
[ UNIQUE ]
[   NO SCROLL
  | DYNAMIC SCROLL
  | SCROLL
  | INSENSITIVE
  | SENSITIVE
]
CURSOR FOR
{ select-statement
| statement-name
      [ FOR { UPDATE [ cursor-concurrency ] | READ ONLY } ]
| call-statement }

 Syntax 2 [SP]
DECLARE cursor-name
[   NO SCROLL
  | DYNAMIC SCROLL
  | SCROLL
  | INSENSITIVE
  | SENSITIVE
]
CURSOR FOR
{ select -statement
     [ FOR { UPDATE [ cursor-concurrency ] | READ ONLY } ]
| call-statement
| USING variable-name }

cursor-name :   identifier

statement-name :    identifier | hostvar

variable-name :   identifier

cursor-concurrency :
BY { VALUES | TIMESTAMP | LOCK }


It says, both ESQL & SP syntax for CALL statement, removing all the optional parameters, basically its the same syntax.
ESQL
DECLARE cursor-name
CURSOR FOR
{ call-statement }

SP
DECLARE cursor-name
CURSOR FOR
{ call-statement}


I want to use
"DECLARE cursor-name CURSOR FOR { call-statement}"
Dont like temp tables.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 9

Author Comment

by:Jerry_Pang
ID: 9932561
tell me if this is a lie, that this is a typo mistake in the helpfiles.
0
 
LVL 9

Author Comment

by:Jerry_Pang
ID: 9932574
create procedure test(
)
begin
select table_name from SYS.SYSTABLE;
end

create procedure get_table_name(
  in id_value int, out tabname char(128)
)
begin
    declare crsr cursor FOR CALL TEST();

    open crsr;
    fetch crsr into tabname;
    close crsr;
end

i want this simplest example to work. but how. is there sumthing wrong with this statement?
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9932586
Jerry,

Are you using SQL Anywhere or Sybase Adaptive Server?
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 500 total points
ID: 9932704
Here is a working example. This will work in Sybase Anywhere.


alter procedure DBA.sp_Test() /* parameters,... */
begin
  select table_name from SYS.SYSTABLE
end

alter procedure DBA.sp_Parent() /* parameters,... */
begin
  declare tabname varchar(40);
  declare i integer;
  declare crsr dynamic scroll cursor for call DBA.Sp_Test();
  open crsr;
  set i=1;
  while i <= 10 loop
    fetch next crsr into tabname;
    set i=i+1;
    select tabname
  end loop;
  close crsr
end

0
 
LVL 9

Author Comment

by:Jerry_Pang
ID: 9932773
now, why is that working.. can you tell me whats wrong with this statement?

create procedure pGenerateInvoice(
IN @xCustomerCode varchar(20) DEFAULT NULL,
IN @xDATE date DEFAULT NULL
)
BEGIN
DECLARE rowcnt integer;
DECLARE lcDate DATE;
    IF isdate(@xDATE)=0 THEN
    /*** then not a valid date ***/
        SET lcDATE= DATE(NOW());
    ELSE
        SET lcDATE = CAST(@xDATE as DATE);
    END IF;

DECLARE MonthlyCharges CURSOR FOR CALL pgetservices();
END
0
 
LVL 9

Author Comment

by:Jerry_Pang
ID: 9932780
think i figured it out. let me try experiment with it a litt.e thanx. let u know if its working.
0
 
LVL 9

Author Comment

by:Jerry_Pang
ID: 9933223
last question,somethings wrong with my BEGIN transaction syntax,
where should i put this or is there a diferent syntax? my begin commit
 and transaction does not work. last task before my final code works.
thanx, one more thing, is this TSQL? ESQL? OR WATCOM?
how can you tell?

create procedure sp_Parent() /* parameters,... */
begin
  declare tabname varchar(40);
  declare i integer;
  declare crsr cursor for call Sp_Test();
  open crsr;
  set i=1;
BEGIN TRANSACTION;
  while i <= 10 loop
    fetch next crsr into tabname;
    set i=i+1;
    select tabname
  end loop;
COMMIT TRANSACTION;
  close crsr
end

0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9933288
To loop thru all  rows use this: Begin Transaction does not work within ESQL.
alter procedure DBA.sp_Parent() /* parameters,... */
begin
  declare err_notfound exception for sqlstate value '02000';
  declare tabname varchar(40);
  declare i integer;
  declare crsr dynamic scroll cursor for call DBA.Sp_Test();
  open crsr;
  MyLoop: loop
    fetch next crsr into tabname;
    if sqlstate = err_notfound then
      leave MyLoop
    end if;
    select tabname
  end loop MyLoop;
  close crsr
end
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9933309
In ESQL, you will do BEGIN ATOMIC instead,

Example:

alter procedure DBA.sp_Parent() /* parameters,... */
begin
  declare err_notfound exception for sqlstate value '02000';
  declare tabname varchar(40);
  declare i integer;
  begin atomic
    declare crsr dynamic scroll cursor for call DBA.Sp_Test();
    open crsr;
    MyLoop: loop
      fetch next crsr into tabname;
      if sqlstate = err_notfound then
        leave MyLoop
      end if;
      select tabname
    end loop MyLoop;
    close crsr
  end
end

HTH

0
 
LVL 9

Author Comment

by:Jerry_Pang
ID: 9933325
gosh. i hav to read alot about esql & tsql.
Give up on stored procedure.
Will convert this code into clientside code.
thanx for the help

the code works but another one come up. I'll be going home now.
Probobly overtime tomorow.
thanx
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9933337
To answer your last question, you can only tell whether a sp is ESQL or TSQL by looking at the syntax/statements only. TSQL object can be migrated to Adaptive Server Enterpise, but ESQL cannot be migrated without changes.

Hope that answers all your questions.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9933341
One correction. You can use atomic keyword only when insert, delete or update statements are inside "begin atomic" structure. A result set cannot be returned inside "begin atomic" structure.

alter procedure DBA.sp_Parent() /* parameters,... */
begin
  declare err_notfound exception for sqlstate value '02000';
  declare tabname varchar(40);
  declare i integer;
   declare crsr dynamic scroll cursor for call DBA.Sp_Test();
    open crsr;
    MyLoop: loop
      fetch next crsr into tabname;
      if sqlstate = err_notfound then
        leave MyLoop
      end if;
      select tabname
      begin atomic
         /* update, delete or insert statements */
       end
    end loop MyLoop;
    close crsr
  end

0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question