How do I convert this Access query for SQL Server? [closed]

Question

I have a Query used in Access which does not work in SQL Server as it stands:

SELECT 
  mstPatient.PatientID, 
  mstPatient.PatientID AS PKNO, 
  IIf(IsNull([mstPatient].[PPrefix]),'',[mstPatient].[PPrefix]+' ') 
    + [FirstName] + IIf(IsNull([MiddleName]),'',+' '+[MiddleName])
    + ' ' + [LastName] AS PatientName, 
  mstPatient.AgeYears, 
  mstPatient.Gender, 
  mstPatient.RegistrationDate, 
  mstPatient.City, 
  mstPatient.Occupation, 
  mstPatient.TieupID, 
  SCDoctorList.Doctor_Name, 
  SCDoctorList.DDesignation, 
  mstPatient.MobileNo, 
  mstPatient.TelNo, 
  mstPatient.RefBy, 
  DateDiff('yyyy',mstPatient.BirthDate,Date()) AS Years, 
  mstPatient.Area, 
  mstPatient.Caste, 
  mstPatient.PatientIdPrivate, 
  mstPatient.ClinicName,
  IIf(mstPatient!Add1<>'', mstPatient!Add1+Chr(13),'') 
    + IIf(mstPatient!Add2<>'', mstPatient!Add2+Chr(13),'') 
    + IIf(mstPatient!Add3<>'', mstPatient!Add3+Chr(13),'')   
    + IIf(mstPatient!City<>'', mstPatient!City+Chr(13),'') 
    + IIf(mstPatient!PinCode<>'','-'+mstPatient!PinCode+Chr(13),'') 
    + IIf(mstPatient!State<>'',mstPatient!State+Chr(13),'') 
    + IIf(mstPatient!Country<>'',mstPatient!Country,'') AS Address, 
  mstPatient.RemarkLang, 
  mstPatient.RFirstName + ' ' + mstPatient.RMiddleName+' ' 
    + mstPatient.RLastName AS RPatientName, 
  mstPatient.RArea, 
  mstPatient.RCity, 
  mstPatient.FamilyId 
FROM mstPatient 
INNER JOIN SCDoctorList ON mstPatient.InChargeDoctor = SCDoctorList.DoctorID;

Answer 1

From a quick glance it looks like you need to:

1. Replace your IIF statements with CASE expressions
2. Change the colums referenced by ! i.e. mstPatient!Add1 becomes mstPatient.Add1
3. Change CHR(13) to CHAR(13)

How to convert IIF to CASE

From Access:

IIf(IsNull([mstPatient].[PPrefix]),'',[mstPatient].[PPrefix]+' ') 
    + 'My postfix string'  AS MyIIFStatement

Becomes in SQL Server (using IS NULL check):

CASE 
    WHEN [mstPatient].[PPrefix] IS NULL THEN ''
    ELSE [mstPatient].[PPrefix] + ' '
END + 'My postfix string'  AS MyCaseStatement

Note: SQL Server also has an ISNULL & COALESCE function that can be used to return a default value when the source value is null, I've not used it this case as there is a difference in the result when the source value is null ie: ' ' vs ''