Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Possible Duplicate:
How do I convert this Access query for SQL Server?

SELECT mstDoctor.DoctorID, IIf(DPrefix<>'',DPrefix+' '+DName,DName) AS Doctor_Name, mstDoctor.City, mstDoctor.MobileNo, mstDoctor.TelNo, mstDoctor.FaxNo, mstDoctor.DDesignation, mstDoctor.DSpecialization, mstDoctor.PANNo FROM mstDoctor WHERE (((mstDoctor.DoctorID)>0));

share|improve this question

marked as duplicate by Mark Storey-Smith, Marian, Derek Downey, RolandoMySQLDBA, Shawn Melton Aug 10 '11 at 15:22

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

4  
While I don't think it's an exact duplicate, I do feel it is offtopic to dba as this shows absolutely no motivation to learn the information himself (just have others do his work). -1 –  Derek Downey Aug 10 '11 at 13:29
2  
It is off topic. I'm going to devote my evening after work (in about 9-10 hours) to deciding what to do in this case. –  jcolebrand Aug 10 '11 at 14:52

2 Answers 2

SELECT mstDoctor.DoctorID, 
       Doctor_Name = CASE WHEN DPrefix<>'' THEN DPrefix +' '+DName ELSE DName END, 
       mstDoctor.City, mstDoctor.MobileNo, mstDoctor.TelNo, mstDoctor.FaxNo, 
       mstDoctor.DDesignation, mstDoctor.DSpecialization, mstDoctor.PANNo 
FROM mstDoctor 
    WHERE ( mstDoctor.DoctorID > 0 ) 

I don't know nothing about your database schema, maybe you should check the field DPrefix not only for empty strings (''), but for NULL values too.

share|improve this answer

Slightly different approach (mostly to avoid repeating column names - though CASE is usually how you would translate IIF to T-SQL).

SELECT
    DoctorID,
    Doctor_Name = COALESCE(NULLIF(DPrefix, '') + ' ', '') + DName, 
    ...other columns...
FROM
    dbo.mstDoctor
WHERE
    DoctorID > 0;
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.