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