Solved

DECLARE cursorname CURSOR FOR CALL storedprocedure(a,b)

Posted on 2003-12-12
15
2,680 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Find out what Office 365 Transport Rules are, how they work and their limitations managing Office 365 signatures.
A Short Story about the Best File Recovery Software – Acronis True Image 2017
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now