Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DECLARE cursorname CURSOR FOR CALL storedprocedure(a,b)

Posted on 2003-12-12
15
Medium Priority
?
2,729 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Read on to get a few ideas on how to promote your next corporate event.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

704 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