• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1205
  • Last Modified:

Creating Rapid SQL Cursor's????

I am trying to create a cursor in Rapid SQL when I exceute the cursor in MS SQL it executes fine when I move it to Rapid SQL i get the following error "Declare Curosr must be the only statement in the querty batch"

Are there any syntax differeneces from running cursors's in Rapid SQL from MS
DECLARE match_cursor Cursor  
FOR 
    SELECT DISTINCT Owner FROM dbo.ScheduledJob
    GO
OPEN match_cursor 
    DECLARE @DOC_NUM char(10)
   
    FETCH NEXT FROM match_cursor INTO @DOC_NUM
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	SELECT @DOC_NUM
FETCH NEXT FROM match_cursor INTO @DOC_NUM
END
CLOSE match_cursor
DEALLOCATE match_cursor
GO

Open in new window

0
LJordanMS
Asked:
LJordanMS
1 Solution
 
reb73Commented:
Remove the GO command from line 4
0
 
grant300Commented:
O.K., first of all, you are confusing the tools and the databases.

The databases you are using are Sybase (some unspecified product and version but we assume it is such because you posted it in the Sybase area) and MS SQL Server.

The tool you are using with Sybase is RapidSQL; an database IDE, not a database in and of itself.  There is no mention of the tool you are using with MS SQL Server to issue queries.

Next, Sybase is telling you that you have to run the DECLARE CURSOR statement separately.

BEGIN
  DECLARE CURSOR.......
END
go
BEGIN
   .... the rest of your code.....
END
go

Regards,
Bill
0
 
LJordanMSAuthor Commented:
The tool that I am using in MS SQL is Microsoft SQL Server Management Studio, some old projects are using Embarcadero Rapid SQL which is connecting to Sybase.  

I am trying to understand the syntax that Embarcadero Rapid SQL uses for cursors, it doesn't seem to confrom to my understanding of SQL cursors (MS).
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
grant300Commented:
This issue you are having has nothing to do with RapidSQL, you are encountering differences between Sybase and MS SQL Server.

Sybase has it's rules and you have to obey them.  MS SQL Server is a derivative of an old version of Sybase ASE.  MS has loosened up on some of the rules that Sybase instantiated, usually to protect data and transaction integrity.

Cursors are almost exclusively used by applications and in stored procedures.  Using them in an interactive query is just not something one would do.  If you need to write procedural code to do something, create a stored procedure and be done with it.  Then you can test basic syntax at compile time and debug it with an interactive debugger at run time.

I assume the example you gave above was just that, an example, and not an actual requirement.  Clearly what you have there could be done in a single SQL statement.

Regards,
Bill
0
 
alpmoonCommented:
As much as I understand Rapid SQL is running the whole code as one batch rather than two. It looks like the first 'go' is ignored since it is not in the first column. The solution is either you can create a stored proc. to run the whole thing once as Bill suggested, or you can try to move the first "go" to the first column:

DECLARE match_cursor Cursor  
FOR
    SELECT DISTINCT Owner FROM dbo.ScheduledJob
GO
OPEN match_cursor
    DECLARE @DOC_NUM char(10)
   
    FETCH NEXT FROM match_cursor INTO @DOC_NUM
WHILE (@@FETCH_STATUS <> -1)
BEGIN
      SELECT @DOC_NUM
FETCH NEXT FROM match_cursor INTO @DOC_NUM
END
CLOSE match_cursor
DEALLOCATE match_cursor
GO
0
 
LJordanMSAuthor Commented:

Thats what I am trying to, is build my stor proc by running and testing it before I complie it into a stor proc. I will just complie it into a stor proc as you suggest and test the script that way I am to use to MS by running parts of the SQL code snippets before bringing it all together into one stor proc.

Thanks for the help Bill
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now