CODEWITHSUNDEEP
sybase
Mayank
Mayank

Reputation: 1631

Can I use MS SQL syntax in Sybase?

I have worked on SQL Server database. Now I have to work on a Sybase database (using a Squirrel client). This query is not working : 

DECLARE @tableName VARCHAR(500);
DECLARE my_cursor CURSOR FOR

SELECT name
FROM   sysobjects
WHERE  type = 'U';
OPEN my_cursor;
FETCH NEXT FROM my_cursor INTO @tableName;
WHILE @@FETCH_STATUS = 0
    BEGIN
        //Do something here
        FETCH NEXT FROM my_cursor;
    END
CLOSE my_cursor;
DEALLOCATE CURSOR my_cursor;

It gives an error - Incorrect syntax near the keyword 'FROM'. SQLState: ZZZZZ ErrorCode: 156 Error occured in: FETCH NEXT FROM my_cursor INTO @table_Name

Now this works fine in a SQL Server database (after I change the last line to DEALLOCATE my_cursor). Can anybody tell me where I am going wrong?

Upvotes: 3

Views: 26947

Answers (4)

SmartDeveloper
SmartDeveloper

Reputation: 61

First of all Squirrel supports go as a SQL batch separator. Go to menu item Session--> Session Properties---> 'SQL' Tab.

Scroll to bottom and set 'Statement Separator' as 'go' (quotes not needed) .

Then follow the previous answer . The DECLARE CUROSR can be the only SQL statement in a batch , hence you must insert go after it.

In the next batch re-declare any variables that were declared in earlier batch and will be referenced in second batch.

This should work. This is how I have been testing SQL code involving cursors for years.

Upvotes: 0

AntDC
AntDC

Reputation: 1917

Declare @tablename after the cursor.

Upvotes: 1

AdamH
AdamH

Reputation: 1351

As Mitch points out the fetch syntax is:

fetch cursor_name [into fetch_target_list]

You also need to declare the cursor in a separate batch, this means you must put a "GO" after the declare statement. You will then find that your variable drops out of scope, so you'll need to move that so that it's after the "GO".

You also need to examine @@sqlstatus to see how successful the fetch was, rather than @@FETCH_STATUS which I think is MSSQL only.

DECLARE my_cursor CURSOR FOR
  SELECT name
  FROM   sysobjects
  WHERE  type = 'U'
go

DECLARE @tableName VARCHAR(500)
set nocount on
OPEN my_cursor
FETCH my_cursor INTO @tableName

WHILE @@sqlstatus = 0
  BEGIN
      --Do something here

      FETCH my_cursor INTO @tableName
      print @tablename
  END

CLOSE my_cursor
DEALLOCATE CURSOR my_cursor

And no semicolons needed at the end of lines in Sybase ASE.

Upvotes: 8

Mitch Wheat
Mitch Wheat

Reputation: 300539

DECLARE @tableName VARCHAR(500);
DECLARE my_cursor CURSOR FOR
  SELECT name
  FROM   sysobjects
  WHERE  type = 'U';

OPEN my_cursor;
FETCH my_cursor INTO @tableName;

WHILE @@FETCH_STATUS = 0
  BEGIN
      //Do something here

      FETCH my_cursor INTO @tableName;
  END

CLOSE my_cursor;
DEALLOCATE CURSOR my_cursor;

Upvotes: 1

Related Questions