sp_maysam

USE [KhabarioWordDB]
GO
/****** Object:  StoredProcedure [dbo].[maysam]    Script Date: 2/27/2016 4:52:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[maysam]
  @matn NText,
  @docID INT,
  @positionInDocumentID INT
AS
BEGIN
  delete from DocAllWords where Docid=@docID and positionInDocumentID=@positionInDocumentID
  declare @shortmatn nvarchar(max)
  declare @start_location int
  set @start_location = 0
  set @shortmatn = @matn
  declare @fromStart int
  set @fromStart = 0
  -- SET NOCOUNT ON added to prevent extra result sets from
  SET NOCOUNT ON;
  DECLARE @badWordTypeId INT
  set @badWordTypeId = 100

  DECLARE @ReturnValue nVARCHAR(MAX)
  declare @delimiter varchar(1)
  set @delimiter = ' '

  DECLARE @positionInText INT
  set @positionInText = 0
  declare @done Bit
  set @done = 0
  WHILE @done = 0
  BEGIN

    set @positionInText = @positionInText + 1

    DECLARE @NextFoundIndex INT
    DECLARE @localWordId INT
    DECLARE @isStopWord INT

    SET @NextFoundIndex = CHARINDEX(@delimiter, @shortmatn, @start_location)
	if @NextFoundIndex > 0
	begin
      SET @ReturnValue = SUBSTRING(@shortmatn, @start_location , @NextFoundIndex-@start_location)
	  set @start_location = @NextFoundIndex+1
	end
    else
    begin
		set @fromStart = @fromStart + @start_location
		SET @shortmatn = SUBSTRING(@Matn, @fromStart, @@TEXTSIZE) 
		SET @NextFoundIndex = CHARINDEX(@delimiter, @shortmatn, @start_location)
		set @fromStart = @fromStart + @NextFoundIndex
		if @NextFoundIndex > 0
		begin
			SET @ReturnValue = SUBSTRING(@shortmatn, @start_location , @NextFoundIndex-@start_location)
			set @start_location = @NextFoundIndex+1
		end
		else
		begin
			set @ReturnValue = @shortmatn
			set @done = 1
		end
    end

    if len(@ReturnValue) = 0
    begin
      continue
    end

    set @localWordId = null
    select @localWordId = WordID from Words where Word = @ReturnValue

    if @localWordId is null
    begin
      INSERT Words (Word) VALUES (@ReturnValue)
      set @localWordId = SCOPE_IDENTITY()
    end
    insert into DocAllWords(DocID, WordID, positionInDocumentID, positionInText) Values(@docId, @localWordId, @positionInDocumentID, @positionInText)
  END
  
	delete from DocWords where DocID=@docID and positionInDocumentID=@positionInDocumentID 
	insert into DocWords(DocID, WordID, positionInDocumentID, Frequency)
	select DocID, DAW.WordId, PositionInDocumentID, count(*)
	from DocAllWords DAW left join WordType on DAW.WordId=WordType.WordID and TypeID = @badWordTypeId
	where TypeID is null and DocID=@docID and PositionInDocumentID=@positionInDocumentID 
	group by DocId, PositionInDocumentId, DAW.WordID      
END