Sybase ASE: how to print all table rows using cursor?

The following code is supposed to print all rows contained in the temporary table #table_A:

create table #table_A
    (
     ID                  int          NULL ,
     foo                 int          NULL 
    )

go

insert into #table_A values (1, 2)
insert into #table_A values (2, 3)
go

declare c_table_A cursor                     
    for select *                                    
          from #table_A                      
         order                                      
            by 1                                                                      

open c_table_A                               
  fetch c_table_A                            
  while @@sqlstatus = 0                             
    begin                                           
      print '%1!', c_table_A                 
      fetch c_table_A                        
    end                                             
close c_table_A   


go  

However, it results in the following error message:

DECLARE CURSOR must be the only statement in a query batch.  

How can I print all rows contained in a (temporary) table?

---

Here’s another way of putting my question:

I’m trying to do something like that:

open c_table_A                               
  fetch c_table_A into @record_variable                           
  while @@sqlstatus = 0                             
    begin                                           
      print '%1!', @record_variable
      fetch c_table_A into @record_variable                       
    end                                             
close c_table_A   

Is there a way to declare a variable containing a whole row of a table in sybase?

---

P.S.: just using “select * from …” doesn’t work for me. I need to do some stuff with each of the rows before printing the row. (My question is supposed to focus on the essential part, which is why I didn’t go into any further details regarding other things I need to do with each row)

Answer

Thanks for the clarification.

In a SQL batch, rather than a stored procedure the cursor declaration must be separate from the batch that uses it, hence there needs to be a go between the declare cursor and the subsequent batch.

There is no way to define a “row variable” in Sybase ASE, sorry. Each column returned into a variable must have a variable declared for it. In the example below @id and @foo are declared as the same types as the columns id and foo in the table. Other RDBMS do have “record data types”, but unfortunately not Sybase ASE.

Before committing yourself to using a cursor, which on a large table would be relatively slow, you might be able to perform your other processing in a select statement. If there is conditional logic case ... when ... then ... else ... end could prove useful, though calling a stored procedure directly from within a select statement is not possible, you can call a SQL user defined function. That’s probably a separate question if you need help.

I’ve added a deallocate cursor statement as well, it’s part of the syntax and frees up internal workspaces associated with your connection.

You may want to execute set nocount on before running the batch, it removes the sometimes annoying (1 row affected) message.

set nocount on
go

create table #table_A
    (
     ID                  int          NULL ,
     foo                 int          NULL 
    )
go

insert into #table_A values (1, 2)
insert into #table_A values (2, 3)
go

declare c_table_A cursor                     
    for select *                                    
          from #table_A                      
         order                                      
            by 1                                                                      
go

declare
    @id     int,
    @foo    int

open c_table_A                               
fetch c_table_A into @id, @foo

while @@sqlstatus = 0                             
begin                                           
    print 'id: %1! foo: %2!', @id, @foo
    fetch c_table_A into @id, @foo
end                                             

close c_table_A   
go 

deallocate cursor c_table_A
go