-- dynamic SQL statements expects parameter of type 'ntext/nchar/nvarchar'.
declare @SQLString  nvarchar(4000), @ParmDefinition nvarchar(4000)
-- the third parameter passed in the dynamic statement as by output, returns the length of
-- the hypotenuses of a right triangle, two first parameters are lengths of legs of a triangle
declare  @nHypotenuse float
select @SQLString = 'select @nHypotenuse = sqrt(square(@nLeg1_of_a_triangle)+square(@nLeg2_of_a_triangle))',
@ParmDefinition = '@nLeg1_of_a_triangle float, @nLeg2_of_a_triangle float, @nHypotenuse float out'
-- we call the dynamic statement in such a way
exec sp_executesql @SQLString, @ParmDefinition, @nLeg1_of_a_triangle = 3.0, @nLeg2_of_a_triangle = 4.0, @nHypotenuse = @nHypotenuse  out 
-- or in such a way
exec sp_executesql @SQLString, @ParmDefinition,  3.0,  4.0,  @nHypotenuse  out 
select @nHypotenuse -- Displays 5.0

CREATE PROCEDURE SP_GETALLWORDSFROMTEXT 
  @TableName sysname,  @FieldIdName  sysname,   @FieldIdValue sql_variant,  @FieldTextName  sysname,  @cDelimiters  nvarchar(256) = NULL
AS
-- this Stored procedure inserts the words from a text field into the table.
-- WORDNUM  int – Sequence number of a word
-- WORD nvarchar(4000) – the word
-- STARTOFWORD int – position  in the  text field, with which the word starts
-- LENGTHOFWORD  smallint – length of the word
-- Parameters
-- @TableName name of the table with the text or ntext field
-- @FieldIdName name of Id field
-- @FieldIdValue  value of  Id field
-- @FieldTextName  name of field text or ntext
-- @cDelimiters Specifies one or more optional characters used to separate words in the text field
    begin
          set nocount on
 
          declare @k int, @wordcount int, @nBegSubString int, @nEndSubString int, @nEndString int, @divisor tinyint, @flag bit,  @RetTable bit,
                       @cString nvarchar(4000),  @TypeField varchar(13),  @SQLString  nvarchar(4000), @ParmDefinition   nvarchar(500),  @nBegSubString1 smallint, @nEndSubString1 smallint
           select @TableName =  object_name(object_id(lower(ltrim(rtrim(@TableName))))),  @FieldIdName =  lower(ltrim(rtrim(@FieldIdName))), @FieldTextName =  lower(ltrim(rtrim(@FieldTextName))),
                      @cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words.
                      @nBegSubString = 1, @nEndSubString = 4000, @flag = 0,  @RetTable = 0, @wordcount = 0
 
          -- If the temporary table is not created in the calling procedure, we create the temporary table 
          if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is  null
              begin
                  create  table #GETALLWORDSFROMTEXT (WORDNUM  int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD  smallint)
                  select @RetTable = 1
             end  
          -- we use the dynamic SQL statement to receive the  exact name of text field
           -- as we can write names of fields by a call of the given stored procedure in the arbitrary register
          -- in the string of parameters definition @ParmDefinition we use a keyword output
          -- and by a call of the dynamic SQL statement exec sp_executesql @SQLString, @ParmDefinition,   @FieldTextName  =  @FieldTextName  output 
          -- Also we use a keyword  output for definite before parameter
           select   @SQLString =  'select @FieldTextName = name from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+''''
           select    @ParmDefinition  = '@FieldTextName sysname  output' 
           exec sp_executesql @SQLString, @ParmDefinition,   @FieldTextName  =  @FieldTextName  output
 
          -- we use the dynamic SQL statement to receive the  exact name of Id field
           select   @SQLString =  'select @FieldIdName = name from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldIdName+''''
           select    @ParmDefinition  = '@FieldIdName sysname  output' 
           exec sp_executesql @SQLString, @ParmDefinition,   @FieldIdName  =  @FieldIdName  output
 
          -- we use the dynamic SQL statement to receive the type of field (text or ntext)
           select   @SQLString =  'select @TypeField = name from systypes where xtype = any ( select xtype from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+''')'
           select    @ParmDefinition  = '@TypeField  varchar(13)  output' 
           exec sp_executesql @SQLString, @ParmDefinition,   @TypeField  =  @TypeField  output
 
           select @divisor = case  @TypeField  when  'ntext'   then 2 else 1 end  --  2 for unicode
 
          -- we use the dynamic SQL statement to receive a length of  the text field
           select   @SQLString =      'select @nEndString = 1 + datalength('+ @FieldTextName+')/'+cast( @divisor as nchar(1)) +'  from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50))
           select    @ParmDefinition  = '@nEndString  int  output' 
           exec sp_executesql @SQLString, @ParmDefinition,   @nEndString  =  @nEndString  output
 
           --  We cut the text field into substrings of length no more than 4000 characters and we work with substrings in cycle
          while 1 > 0
                   begin
                    -- we use the dynamic SQL statement to receive a substring of a type nvarchar(4000) from text field
                       select   @SQLString = 'select @cString =  substring('+ @FieldTextName+','+cast( @nBegSubString as nvarchar(20)) +',' + 
                                     cast( @nEndSubString - @nBegSubString + 1 as nvarchar(20))+') from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50))
                       select    @ParmDefinition  = '@cString   nvarchar(4000)  output' 
                       exec sp_executesql @SQLString, @ParmDefinition,   @cString  =  @cString  output
 
                       select   @nBegSubString1 =  1, @nEndSubString1  = @nEndSubString - @nBegSubString +1
 
                       while charindex(substring(@cString, @nBegSubString1, 1)  COLLATE Latin1_General_BIN,  @cDelimiters COLLATE Latin1_General_BIN) > 0 and @nEndSubString >=@nBegSubString -- skip  the character  not in word, if any
                                select  @nBegSubString = @nBegSubString + 1 , @nBegSubString1 = @nBegSubString1 + 1
 
                        while charindex(substring(@cString, @nEndSubString1, 1)  COLLATE Latin1_General_BIN,  @cDelimiters COLLATE Latin1_General_BIN) = 0 and @nEndSubString >=@nBegSubString -- skip  the character in word, if any
                               select  @nEndSubString = @nEndSubString - 1, @nEndSubString1 = @nEndSubString1 - 1 
 
                      if @nEndSubString >=@nBegSubString
                            begin
                                 select top 1  @wordcount = WORDNUM from #GETALLWORDSFROMTEXT order by WORDNUM desc
                                 select  @cString = substring(@cString, @nBegSubString1, @nEndSubString1-@nBegSubString1+1)
                                 -- we use a function GETALLWORDS which one works with strings of a type nvarchar(4000)
                                 -- we add outcome result in the temporary table
                                 insert into #GETALLWORDSFROMTEXT  (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) 
                                 select (@wordcount+WORDNUM), WORD, (@nBegSubString+STARTOFWORD-1), LENGTHOFWORD from      dbo.GETALLWORDS(@cString, @cDelimiters) 
 
                                select @nBegSubString = @nEndSubString + 1,  @nEndSubString = @nEndSubString + 4000
                            end
                      else
                            select  @nEndSubString = @nEndSubString + 4000 -- In a case if the substring consists of one delimiter
 
                     if  @flag = 1  
                               break
                       if @nEndString <= @nEndSubString
                            select @flag = 1,  @nEndSubString = @nEndString
          end
 
       -- If in a calling procedure the table was not created,  we show the result
        if @RetTable = 1
              select * from #GETALLWORDSFROMTEXT
 
    end
GO

declare @cDelimiters nvarchar(256) 
select @cDelimiters = '"-,.:!?«»()'+SPACE(1)+CHAR(9)+CHAR(10)+CHAR(13)+CHAR(12) 
 
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null
      drop table #GETALLWORDSFROMTEXT
create  table #GETALLWORDSFROMTEXT (WORDNUM  int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD  smallint)
exec dbo.SP_GETALLWORDSFROMTEXT 'Your Table name',  'Your  Id field name',  Value of Id field,  ' text or ntext field name',  @cDelimiters
 
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null
    select * from #GETALLWORDSFROMTEXT

-- GETALLWORDS() User-Defined Function Inserts the words from a string into the table.
 -- GETALLWORDS(@cString[, @cDelimiters])
 -- Parameters
 -- @cString  nvarchar(4000) - Specifies the string whose words will be inserted into the table @GETALLWORDS. 
 -- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString.
 -- The default delimiters are space, tab, carriage return, and line feed. Note that GETALLWORDS( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter. 
 -- Return Value table 
 -- Remarks GETALLWORDS() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.
 -- Example
 -- declare @cString nvarchar(4000)
 -- set @cString = 'The default delimiters are space, tab, carriage return, and line feed. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.'
 -- select * from  dbo.GETALLWORDS(@cString, default)     
 -- select * from dbo.GETALLWORDS(@cString, ' ,.')              
 -- See Also GETWORDNUM() , GETWORDCOUNT() User-Defined Functions   
CREATE function GETALLWORDS  (@cString nvarchar(4000), @cDelimiters nvarchar(256))
returns  @GETALLWORDS  table (WORDNUM  smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD  smallint)
    begin
         declare @k smallint, @wordcount smallint, @nEndString smallint, @BegOfWord smallint, @flag bit

         select   @k = 1, @wordcount = 1,  @BegOfWord = 1,  @flag = 0,  @cString =  isnull(@cString, ''), 
                  @cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words.
                  @nEndString = 1 + datalength(@cString) /(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode

                     while 1 > 0
                         begin
                                if  @k - @BegOfWord  >  0 
                                     begin
                                          insert into @GETALLWORDS (WORDNUM,  WORD, STARTOFWORD, LENGTHOFWORD)    values( @wordcount, substring(@cString, @BegOfWord, @k-@BegOfWord), @BegOfWord,  @k-@BegOfWord )   -- previous word
                                          select  @wordcount = @wordcount + 1,  @BegOfWord = @k 
                                      end   
                                 if  @flag  = 1 
                                        break

                                 while charindex(substring(@cString, @k, 1)  COLLATE Latin1_General_BIN,  @cDelimiters COLLATE Latin1_General_BIN) > 0  and  @nEndString > @k  -- skip  break characters, if any
                                        select @k = @k + 1,  @BegOfWord  = @BegOfWord +  1
                                 while charindex(substring(@cString, @k, 1)  COLLATE Latin1_General_BIN,  @cDelimiters COLLATE Latin1_General_BIN) = 0  and  @nEndString > @k  -- skip  the character in the word
                                        select  @k = @k + 1 
                                 if  @k >= @nEndString 
                                        select  @flag  = 1
                          end 
       return 
    end