You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Is the name of an already declared cursor. If both a global and a local cursor exist with *cursor_name* as their name, *cursor_name* refers to the global cursor if GLOBAL is specified and to the local cursor if GLOBAL is not specified.
41
+
Is the name of an already declared cursor. If both a global and a local cursor exist with *cursor_name* as their name, *cursor_name* refers to the global cursor if `GLOBAL` is specified and to the local cursor if `GLOBAL` is not specified.
43
42
44
43
@*cursor_variable_name*
45
44
Is the name of a **cursor** variable. @*cursor_variable_name* must be of type **cursor**.
46
45
47
46
## Remarks
48
-
Statements that operate on cursors use either a cursor name or a cursor variable to refer to the cursor. DEALLOCATE removes the association between a cursor and the cursor name or cursor variable. If a name or variable is the last one referencing the cursor, the cursor is deallocated and any resources used by the cursor are freed. Scroll locks used to protect the isolation of fetches are freed at DEALLOCATE. Transaction locks used to protect updates, including positioned updates made through the cursor, are held until the end of the transaction.
47
+
Statements that operate on cursors use either a cursor name or a cursor variable to refer to the cursor. `DEALLOCATE` removes the association between a cursor and the cursor name or cursor variable. If a name or variable is the last one referencing the cursor, the cursor is deallocated and any resources used by the cursor are freed. Scroll locks used to protect the isolation of fetches are freed at `DEALLOCATE`. Transaction locks used to protect updates, including positioned updates made through the cursor, are held until the end of the transaction.
49
48
50
-
The DECLARE CURSOR statement allocates and associates a cursor with a cursor name.
49
+
The `DECLARE CURSOR` statement allocates and associates a cursor with a cursor name.
51
50
52
-
```
51
+
```sql
53
52
DECLARE abc SCROLL CURSOR FOR
54
53
SELECT*FROMPerson.Person;
55
54
```
56
55
57
-
After a cursor name is associated with a cursor, the name cannot be used for another cursor of the same scope (GLOBAL or LOCAL) until this cursor has been deallocated.
56
+
After a cursor name is associated with a cursor, the name cannot be used for another cursor of the same scope (global or local) until this cursor has been deallocated.
58
57
59
58
A cursor variable is associated with a cursor using one of two methods:
60
59
61
-
- By name using a SET statement that sets a cursor to a cursor variable.
60
+
- By name using a `SET` statement that sets a cursor to a cursor variable.
62
61
63
-
```
62
+
```sql
64
63
DECLARE @MyCrsrRef CURSOR;
65
64
SET @MyCrsrRef = abc;
66
65
```
67
66
68
67
- A cursor can also be created and associated with a variable without having a cursor name defined.
69
68
70
-
```
69
+
```sql
71
70
DECLARE @MyCursor CURSOR;
72
71
SET @MyCursor = CURSOR LOCAL SCROLL FOR
73
72
SELECT * FROM Person.Person;
74
73
```
75
74
76
-
A DEALLOCATE @*cursor_variable_name* statement removes only the reference of the named variable to the cursor. The variable is not deallocated until it goes out of scope at the end of the batch, stored procedure, or trigger. After a DEALLOCATE @*cursor_variable_name* statement, the variable can be associated with another cursor using the SET statement.
75
+
A `DEALLOCATE <@cursor_variable_name>` statement removes only the reference of the named variable to the cursor. The variable is not deallocated until it goes out of scope at the end of the batch, stored procedure, or trigger. After a `DEALLOCATE <@cursor_variable_name>` statement, the variable can be associated with another cursor using the SET statement.
77
76
78
-
```
77
+
```sql
79
78
USE AdventureWorks2012;
80
79
GO
81
80
@@ -90,15 +89,15 @@ SET @MyCursor = CURSOR LOCAL SCROLL FOR
90
89
GO
91
90
```
92
91
93
-
A cursor variable does not have to be explicitly deallocated. The variable is implicitly deallocated when it goes out of scope.
92
+
A cursor variable does not have to be explicitly deallocated. The variable is implicitly deallocated when it goes out of scope.
94
93
95
94
## Permissions
96
-
DEALLOCATE permissions default to any valid user.
95
+
Permissions for `DEALLOCATE` default to any valid user.
97
96
98
97
## Examples
99
98
The following script shows how cursors persist until the last name or until the variable referencing them has been deallocated.
0 commit comments