how to use the NormalizeChars stored procedure inside the searchWord stored Procedure

I want to remove the diacritics from this function

ALTER PROCEDURE [dbo].[SearchWord] (@Word            NVARCHAR(50),
                                    @PageNumber      INT,
                                    @ProductsPerPage INT,
                                    @HowManyResults  INT OUTPUT)
AS
  SET @Word = '%' + RTRIM(@Word) + '%';

  DECLARE @Results TABLE (
    ProductsId          INT,
    ProductsCode        NVARCHAR(250),
    ProductsDesc        NVARCHAR(MAX),
    ProductsIngredients NVARCHAR(MAX),
    ProductsName        NVARCHAR(250),
    ProductsPhoto       NVARCHAR(MAX),
    ProductsPrice       MONEY,
    ProductsWeight2     FLOAT,
    RowNumber           INT)

  -- Obtain the matching products
  INSERT INTO @Results
  SELECT DISTINCT T1.ProductsID,
                  T1.ProductsCode,
                  LOWER(T1.ProductsDesc)                      asProductsDesc,
                  T1.ProductsIngredients,
                  LOWER(T1.ProductsName)                      AS ProductsName,
                  T1.ProductsPhoto,
                  T1.ProductsPrice,
                  T1.ProductsWeight2,
                  ROW_NUMBER() OVER(ORDER BY T1.ProductsName) RowNumber
  FROM开发者_运维技巧   (SELECT ProductsID,
                 ProductsCode,
                 LOWER(ProductsDesc) AS ProductsDesc,
                 ProductsIngredients,
                 LOWER(ProductsName) AS ProductsName,
                 ProductsPhoto,
                 ProductsPrice,
                 ProductsWeight2
          FROM   Products
          WHERE  ProductsName LIKE @Word) AS T1
         FULL OUTER JOIN (SELECT *
                          FROM   Products
                          WHERE  ProductsDesc LIKE @Word) AS T2
           ON T1.ProductsID = T2.ProductsID
  ORDER  BY T1.ProductsPrice

  DELETE FROM @Results
  WHERE  NULLIF([ProductsName], '') IS NULL

  SELECT @HowManyResults = COUNT(*)
  FROM   @Results

  INSERT INTO @Results
  SELECT DISTINCT ProductsId,
                  ProductsCode,
                  ProductsDesc,
                  ProductsIngredients,
                  LOWER(ProductsName) AS ProductsName,
                  ProductsPhoto,
                  ProductsPrice,
                  ProductsWeight2,
                  RowNumber
  FROM   @Results

  SELECT DISTINCT ProductsId,
                  ProductsCode,
                  ProductsDesc,
                  ProductsIngredients,
                  LOWER(ProductsName) AS ProductsName,
                  ProductsPhoto,
                  ProductsPrice,
                  ProductsWeight2,
                  RowNumber
  FROM   @Results R
  WHERE  R.RowNumber > ( @PageNumber - 1 ) * @ProductsPerPage
         AND R.RowNumber <= @PageNumber * @ProductsPerPage
  ORDER  BY R.ProductsPrice ASC  

using this procedure

ALTER PROCEDURE normalizeChars 
(@NAME NVARCHAR(100)) 
AS
BEGIN
  DECLARE @TempString NVARCHAR(100)
  SET @TempString = @NAME 
  SET @TempString = LOWER(@TempString)
  SET @TempString =  REPLACE(@TempString,'à', 'a')
  SET @TempString =  REPLACE(@TempString,'è', 'e')
  SET @TempString =  REPLACE(@TempString,'é', 'e')
  SET @TempString =  REPLACE(@TempString,'ì', 'i')
  SET @TempString =  REPLACE(@TempString,'ò', 'o')
  SET @TempString =  REPLACE(@TempString,'ù', 'u')
  SET @TempString =  REPLACE(@TempString,'ç', 'c')
  SET @TempString =  REPLACE(@TempString,'''', '')
  SET @TempString =  REPLACE(@TempString,'`', '')
  SET @TempString =  REPLACE(@TempString,'-', '')
  SET @TempString =  REPLACE(@TempString,'ά','α')
  SET @TempString =  REPLACE(@TempString,'έ','ε')
  SET @TempString =  REPLACE(@TempString,'ί','ι')
  SET @TempString =  REPLACE(@TempString,'ό','ο')
  SET @TempString =  REPLACE(@TempString,'ή','η')
  SET @TempString =  REPLACE(@TempString,'ύ','υ')
  RETURN @TempString
END

on ProductsName and ProductsDesc

is it possible and how?

You say you are on Greek_CI_AI. In that case most of your mappings already happen (Only the top 3 don't).

;WITH T(col1,col2) AS
(
  SELECT N'''',N'' UNION ALL
  SELECT N'`',N'' UNION ALL
  SELECT N'-',N'' UNION ALL

  SELECT N'à',N'a' UNION ALL
  SELECT N'è',N'e' UNION ALL
  SELECT N'é',N'e' UNION ALL
  SELECT N'ì',N'i' UNION ALL
  SELECT N'ò',N'o' UNION ALL
  SELECT N'ù',N'u' UNION ALL
  SELECT N'ç',N'c' UNION ALL
  SELECT N'ά',N'α' UNION ALL
  SELECT N'έ',N'ε' UNION ALL
  SELECT N'ί',N'ι' UNION ALL
  SELECT N'ό',N'ο' UNION ALL
  SELECT N'ή',N'η' UNION ALL
  SELECT N'ύ',N'υ'
)
SELECT *
FROM T 
WHERE col1<>col2  COLLATE Greek_CI_AI 

Returns

col1 col2
---- ----
'    
`    
-    

Also you don't need to use LOWER as the collation is case insensitive.

If you do need to ignore the remaining 3 characters in your search you could use a scalar UDF as below

CREATE FUNCTION dbo.normalizeChars 
(@Name NVARCHAR(100)) 
RETURNS NVARCHAR(100)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS 
BEGIN
  SET @Name =  REPLACE(@Name,'''', '')
  SET @Name =  REPLACE(@Name,'`', '')
  SET @Name =  REPLACE(@Name,'-', '')
  RETURN @Name
END