------------------ Table Init ------------------ IF OBJECT_ID dbo Depa

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
------------------ Table Init ------------------
IF OBJECT_ID('dbo.Departments') IS NOT NULL
BEGIN
IF OBJECT_ID('dbo.Books') IS NOT NULL
DROP TABLE dbo.Books
DROP TABLE dbo.Departments
END
GO
CREATE TABLE dbo.Departments
(
DepartmentId int IDENTITY(1, 1) NOT NULL,
[Name] varchar(100) NOT NULL,
CONSTRAINT PK_Departments PRIMARY KEY CLUSTERED (DepartmentId),
);
GO
IF OBJECT_ID('dbo.Books') IS NOT NULL
DROP TABLE dbo.Books
GO
CREATE TABLE dbo.Books
(
BookId int IDENTITY(1, 1) NOT NULL,
[Name] varchar(100) NOT NULL,
YearOfPub date NULL,
DepartmentId int NOT NULL,
CONSTRAINT PK_Books PRIMARY KEY CLUSTERED (BookId),
CONSTRAINT FK_Books_Departments FOREIGN KEY (DepartmentId) REFERENCES dbo.Departments (DepartmentId)
);
GO
INSERT INTO dbo.Departments
VALUES
('Novels'),
('Fantasy')
INSERT INTO dbo.Books
VALUES
('War and Peace', '1863-01-01', 1),
('War of the Worlds', NULL, 2)
GO
------------------ 1 ------------------
IF OBJECT_ID('dbo.get_books_cursor1') IS NOT NULL
DROP PROCEDURE dbo.get_books_cursor1
GO
CREATE PROCEDURE dbo.get_books_cursor1
@books_cursor CURSOR VARYING OUTPUT
AS
SET @books_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT BookId, [Name]
FROM dbo.Books
OPEN @books_cursor
GO