Solved

DECLARE cursorname CURSOR FOR CALL storedprocedure(a,b)

Posted on 2003-12-12
15
2,714 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pivot and Unpivot in Sybase 2 1,721
Backing up Sybase database Server using Veeam 3 1,199
SQL Update query 9 363
How to get military time in Sybase? 3 107
Facebook has became the #1 social media platform. People share many funny videos there, yet you don't know how to download them? Now you can download Videos from Facebook in just 3 simple steps.
Arrow Electronics was searching for a KVM  (Keyboard/Video/Mouse) switch that could display on one single monitor the current status of all units being tested on the rack.

685 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