[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dynamic  Cursor

Posted on 2005-04-25
4
Medium Priority
?
307 Views
Last Modified: 2010-03-19

How do I create dyanamic cursors in Sql Server.

I created oracle procedure and I used

TYPE RCR_GENERAL IS REF CURSOR;
    Rcr_Gp RCR_GENERAL;

I am looking for replacement to above piece.

Thanks,
Arora
0
Comment
Question by:Arora
  • 2
4 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13861086
DECLARE rcr_General CURSOR DYNAMIC FOR
   <<SELECT Clause>>


Here's a definition of Dynamic Cursors from Books Online
DYNAMIC Cursors

Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13861109
Look at:

DECLARE @cursor_variable_name CURSOR

in Books Online (topic "DECLARE").  That may not be exact, but I think that's as close as SQL gets.
0
 

Author Comment

by:Arora
ID: 13861546

I am looking something like

Open a cursor for table1 and close
Open a same cursor for table2

If it is not possible, please let me know any alternatives.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 13861625
To do that, you would have to use dynamic SQL and a GLOBAL cursor.


For example:


EXEC('DECLARE csr1 CURSOR GLOBAL FOR SELECT ... FROM table1 WHERE ...')

OPEN csr1
FETCH NEXT FROM csr1 INTO ...
WHILE @@FETCH_STATUS = 0
BEGIN
    ...
    FETCH NEXT FROM csr1 INTO ...
END --WHILE

CLOSE csr1
DEALLOCATE csr1

EXEC('DECLARE csr1 CURSOR GLOBAL FOR SELECT ... FROM table2 WHERE ...')
...


Don't know for sure if that will really do what you need or not, but I think it's all that's available in SQL Server.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

873 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