USE test_data_base GO Object StoredProcedure dbo addWordDictionary Scr

 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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
USE [test_data_base]
GO
/****** Object: StoredProcedure [dbo].[addWordDictionary] Script Date: 27.04.2015 15:43:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[addWordDictionary] (@name_w varchar(20) , @name_d varchar(20))
AS BEGIN
DECLARE @Number_D INT;
DECLARE @Number_W INT;
SELECT @Number_D = COUNT(*) FROM Dictionary WHERE name = @name_d;
SELECT @Number_W = COUNT(*) FROM Word WHERE name = @name_w;
DECLARE @d_id INT;
DECLARE @w_id INT;
BEGIN TRAN;
--назва словника існує
if ((@Number_D > 0) and (@Number_W = 0))
begin
SELECT @d_id = id FROM Dictionary WHERE name = @name_d;
insert into Word (name) values (@name_w);
set @w_id = @@IDENTITY;
insert into Dictionary_Word(word_id,dictionary_id) values (@w_id, @d_id);
PRINT 'INSERT INTO WORD';
end;
else
begin
--назва слова уже існує
if ((@Number_D = 0) and (@Number_W > 0))
begin
SELECT @w_id = id FROM Word WHERE name = @name_w;
insert into Dictionary (name) values (@name_d);
set @d_id = @@IDENTITY;
insert into Dictionary_Word(word_id,dictionary_id) values (@w_id, @d_id);
PRINT 'INSERT INTO DICTIONARY';
end
else
begin
--назва словника і слова не існує до цього
if ((@Number_D = 0) and (@Number_W = 0))
begin
insert into Dictionary (name) values (@name_d);
set @d_id = @@IDENTITY;
insert into Word (name) values (@name_w);
set @w_id = @@IDENTITY;
insert into Dictionary_Word(word_id,dictionary_id) values (@w_id, @d_id);
PRINT 'INSERT INTO WORD';
PRINT 'INSERT INTO DICTIONARY';
end;
end;
END;
--
--
--coursors
BEGIN TRY
DECLARE @name_s VARCHAR(50)
DECLARE @id_s INT;
DECLARE @size INT;
SET @size = 0;
-- Declare the cursor.
DECLARE my_cursor SCROLL CURSOR FOR
SELECT id, name FROM Dictionary
--select count(*) from dbo.Dictionary
OPEN my_cursor;
FETCH FIRST FROM my_cursor INTO @id_s, @name_s ;
--print @name_s ;
set @size = @size + DATALENGTH(@name_s) + DATALENGTH(@id_s);
FETCH ABSOLUTE 27 FROM my_cursor INTO @id_s, @name_s ;
--print @name_s ;
set @size = @size + DATALENGTH(@name_s) + DATALENGTH(@id_s);
FETCH LAST FROM my_cursor INTO @id_s, @name_s ;
--print @name_s ;
set @size = @size + DATALENGTH(@name_s) + DATALENGTH(@id_s);
PRINT CONVERT(varchar(20), @size) + ' byte';
print @@CURSOR_ROWS;
CLOSE my_cursor;
DEALLOCATE my_cursor;
END TRY
BEGIN CATCH
Print 'Sorry, Error :( ';
END CATCH;
COMMIT;
if(@@ERROR <> 0)
ROLLBACK;
END