|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Cirsor Newbie
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Arpan De 2002-11-09, 3:23 am |
| The following stored procedure creates a cursor:
CREATE PROCEDURE CursorExample
AS
DECLARE CurExample CURSOR FOR SELECT * FROM tblProducts
OPEN CurExample
FETCH NEXT FROM CurExample
When the above procedure is executed in the Query Analyzer, though each of the rows existing in the table tblProducts are displayed one by one (after pressing F5 key everytime), after the 1st record gets displayed, 2 error meesages are also shown which says:
1. A cursor with the name 'CurExample' already exists.
2. The cursor is already open
Next if I include the line CLOSE CurExample at the very end of the above stored procedure, only the 1st row gets displayed but at the same time the error message no. 1 shown above also gets displayed. Now if I include the line DEALLOCATE CurExample at the very end of the stored procedure & then execute the stored procedure, though no error messages are shown, only the 1st record gets displayed even after pressing the F5 key repeatedly. Why is this happening? How do I view each of the rows one by one (as what was happening when the above stored procedure was executed for the 1st time) without getting the 2 error messages? Also is it necessary to use WHILE @@FETCH_STATUS=0 to view the rows one by one?
Thanks,
Arpan
| |
| Andrew J. Kelly 2002-11-09, 11:23 am |
| Arpan,
Your missing a LOT of code to properly work with a Cursor. BooksOnLine does have some examples that I suggest you look at. If you have SQL 2000 then there is also sever cursor templates in the Object browser of Query Analyzer. If you show what your trying to do maybe we can suggest a way to do this without a cursor. Cursors should be a last resort.
--
Andrew J. Kelly, SQL Server MVP
TargitInteractive
"Arpan De" <arpan_de5A@rediffmail.com> wrote in message news:eYr4W$8hCHA.1232@tkmsftngp09...
The following stored procedure creates a cursor:
CREATE PROCEDURE CursorExample
AS
DECLARE CurExample CURSOR FOR SELECT * FROM tblProducts
OPEN CurExample
FETCH NEXT FROM CurExample
When the above procedure is executed in the Query Analyzer, though each of the rows existing in the table tblProducts are displayed one by one (after pressing F5 key everytime), after the 1st record gets displayed, 2 error meesages are also shown which says:
1. A cursor with the name 'CurExample' already exists.
2. The cursor is already open
Next if I include the line CLOSE CurExample at the very end of the above stored procedure, only the 1st row gets displayed but at the same time the error message no. 1 shown above also gets displayed. Now if I include the line DEALLOCATE CurExample at the very end of the stored procedure & then execute the stored procedure, though no error messages are shown, only the 1st record gets displayed even after pressing the F5 key repeatedly. Why is this happening? How do I view each of the rows one by one (as what was happening when the above stored procedure was executed for the 1st time) without getting the 2 error messages? Also is it necessary to use WHILE @@FETCH_STATUS=0 to view the rows one by one?
Thanks,
Arpan
|
|
|
|
|