CREATE PROCEDURE [dbo].[MedicalInfoSearch]
    @Type TINYINT,
    @Expression NVARCHAR(MAX)
AS
SELECT TOP (100)
       MedicationInfo.Nezam_No MedicalId,
       MedicationInfo.Name FirstName,
       MedicationInfo.Family LastName,
       Specialty.Grade_Name SpecialtyName,
       CAST(Specialty.Status AS INT) SpecialtyTypeCode
FROM dbo.Doctor MedicationInfo
    JOIN dbo.Grade Specialty
        ON Specialty.Grade_Code = MedicationInfo.Grade_Code
WHERE (
          @Type = 0
          AND MedicationInfo.Nezam_No = @Expression
      )
      OR
      (
          @Type = 1
          AND
          (
              MedicationInfo.Nezam_No LIKE '%' + @Expression + '%'
              OR MedicationInfo.Family LIKE '%' + REPLACE(@Expression, NCHAR(1740), NCHAR(1610)) + '%'
              OR MedicationInfo.Family LIKE '%' + REPLACE(@Expression, NCHAR(1610), NCHAR(1740)) + '%'
              OR MedicationInfo.Family LIKE '%' + REPLACE(@Expression, NCHAR(1705), NCHAR(1603)) + '%'
              OR MedicationInfo.Family LIKE '%' + REPLACE(@Expression, NCHAR(1603), NCHAR(1705)) + '%'
              OR MedicationInfo.Name LIKE '%' + REPLACE(@Expression, NCHAR(1740), NCHAR(1610)) + '%'
              OR MedicationInfo.Name LIKE '%' + REPLACE(@Expression, NCHAR(1610), NCHAR(1740)) + '%'
              OR MedicationInfo.Name LIKE '%' + REPLACE(@Expression, NCHAR(1705), NCHAR(1603)) + '%'
              OR MedicationInfo.Name LIKE '%' + REPLACE(@Expression, NCHAR(1603), NCHAR(1705)) + '%'
          )
      )
ORDER BY MedicationInfo.Nezam_No;
