?
Solved

Can I Declare a dynamic cursor in Sybase

Posted on 2009-12-19
3
Medium Priority
?
1,991 Views
Last Modified: 2012-05-08
I need to declare a cursor in a procedure on a table passed to that procedure.

To put it simply, i want to execute dynamic cursors in my procedure,

I want the below test procedure to work for me, then i can take it from there,

DROP PROCEDURE TEST_PROC
GO

CREATE PROCEDURE TEST_PROC
AS
DECLARE @CODE NUMERIC(10)
DECLARE @LS VARCHAR(10)
DECLARE @LS_SQL VARCHAR(10)

BEGIN
SELECT @LS_SQL = "DECLARE C_PLAN CURSOR FOR
SELECT CODE FROM ABC  WHERE CODE = 100"
EXEC (@LS_SQL)

OPEN C_PLAN      
           
    FETCH  C_PLAN INTO @CODE
    WHILE (@@SQLSTATUS <> 2)        
       
    BEGIN
    SELECT @LS =      CONVERT(VARCHAR,@CODE)
    PRINT @LS
    FETCH  C_PLAN INTO @CODE
      END        
    CLOSE C_PLAN        
    DEALLOCATE CURSOR C_PLAN    
END      
     
EXEC TEST_PROC


Cheers,
Rosh
0
Comment
Question by:diasroshan
3 Comments
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 1500 total points
ID: 26089359
No, you can't do this.

From the Sybase Reference Manual - Commands:

Dynamically executing Transact-SQL  
  • You cannot supply string and char_variable options to execute the following commands: begin transaction, commit, connect to, declare cursor, rollback, dump transaction, dbcc, set, use, or nested execute commands.  
Are you sure you really need a cursor? Almost all of the time there are other ways to achieve the same effect that will be faster and cleaner... and almost all of those would work in dynamic SQL...
0
 
LVL 4

Expert Comment

by:jlsilva01
ID: 26145919
I didn't understand what do you need. But... let's try to answer...

You can't use dynamic cursor, like Joe informations...

However, exists two simple ways to declare cursors as dynamic.

1. Declare a cursor with variable
DECLARE C_PLAN CURSOR FOR SELECT CODE FROM ABC  WHERE CODE = @variable

2. Declare some cursors as needed.
IF @variable = "X"
   DECLARE C_PLAN CURSOR FOR SELECT CODE FROM ABC  WHERE CODE = @variableX  
ELSE IF @variable = "Y"
   DECLARE C_PLAN CURSOR FOR SELECT CODE FROM ABC  WHERE CODE = @variableY
ELSE IF @variable = "Z"
   DECLARE C_PLAN CURSOR FOR SELECT CODE FROM ABC  WHERE CODE = @variableZ

0
 
LVL 18

Author Closing Comment

by:diasroshan
ID: 31668081
I went ahead with a work around. Guess there is no solution to the question asked. Thanks for the effort.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In the below post we have mentioned the best hosting type for startups. Also, check out some of the superlative web hosting companies that are proposing affordable web hosting solutions to host your startup website.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month16 days, 7 hours left to enroll

862 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