DECLARE CURSOR FAST_FORWARD FOR : Declare Cursor « Cursor « SQL Server / T-SQL Tutorial






3>
4> IF OBJECT_ID('dbo.Groups') IS NOT NULL
5>   DROP TABLE dbo.Groups;
6> GO
1>
2> CREATE TABLE dbo.Groups
3> (
4>   groupid  VARCHAR(10) NOT NULL,
5>   memberid INT         NOT NULL,
6>   string   VARCHAR(10) NOT NULL,
7>   val      INT         NOT NULL,
8>   PRIMARY KEY (groupid, memberid)
9> );
10>
11> INSERT INTO dbo.Groups(groupid, memberid, string, val)
12>   VALUES('a', 3, 'stra1', 6);
13> INSERT INTO dbo.Groups(groupid, memberid, string, val)
14>   VALUES('a', 9, 'stra2', 7);
15> INSERT INTO dbo.Groups(groupid, memberid, string, val)
16>   VALUES('b', 2, 'strb1', 3);
17> INSERT INTO dbo.Groups(groupid, memberid, string, val)
18>   VALUES('b', 4, 'strb2', 7);
19> INSERT INTO dbo.Groups(groupid, memberid, string, val)
20>   VALUES('b', 5, 'strb3', 3);
21> INSERT INTO dbo.Groups(groupid, memberid, string, val)
22>   VALUES('b', 9, 'strb4', 11);
23> INSERT INTO dbo.Groups(groupid, memberid, string, val)
24>   VALUES('c', 3, 'strc1', 8);
25> INSERT INTO dbo.Groups(groupid, memberid, string, val)
26>   VALUES('c', 7, 'strc2', 10);
27> INSERT INTO dbo.Groups(groupid, memberid, string, val)
28>   VALUES('c', 9, 'strc3', 12);
29>
30>
31> DECLARE
32>   @Result TABLE(groupid VARCHAR(10), product BIGINT);
33> DECLARE
34>   @groupid AS VARCHAR(10), @prvgroupid AS VARCHAR(10),
35>   @val AS INT, @product AS BIGINT;
36> DECLARE C CURSOR FAST_FORWARD FOR
37>   SELECT groupid, val FROM dbo.Groups ORDER BY groupid;
38> OPEN C
39> FETCH NEXT FROM C INTO @groupid, @val;
40> SELECT @prvgroupid = @groupid, @product = 1;
41> WHILE @@fetch_status = 0
42> BEGIN
43>   IF @groupid <> @prvgroupid
44>   BEGIN
45>     INSERT INTO @Result VALUES(@prvgroupid, @product);
46>     SELECT @prvgroupid = @groupid, @product = 1;
47>   END
48>   SET @product = @product * @val;
49>   FETCH NEXT FROM C INTO @groupid, @val;
50> END
51> IF @prvgroupid IS NOT NULL
52>   INSERT INTO @Result VALUES(@prvgroupid, @product);
53> CLOSE C;
54> DEALLOCATE C;
55>
56> SELECT groupid, product FROM @Result;
57>
58> GO
groupid    product
---------- --------------------
a                            42
b                           693
c                           960
1>
2> drop table Groups;
3> GO








18.2.Declare Cursor
18.2.1.The syntax of the DECLARE CURSOR statement
18.2.2.A DECLARE CURSOR statement that declares a dynamic local cursor
18.2.3.A cursor created in SQL server.
18.2.4.INSENSITIVE Cursor
18.2.5.Cursor on a Single-Column Key
18.2.6.Four cursors
18.2.7.Declare a cursor variable to hold the cursor output variable
18.2.8.Local scroll cursor
18.2.9.DECLARE CURSOR FAST_FORWARD FOR
18.2.10.DECLARE cursor LOCAL
18.2.11.DECLARE CURSOR GLOBAL FOR
18.2.12.Static cursor