I need to check whether the column middle name contain any value or not.
If it is empty, then it should not be concatenated with the name.
FirstName + ' ' MiddleName + ' ' + LastName as Name,
You could use
CASE expression with
COALESCE in following:
select .. Agent, case when coalesce(MiddleName, '') = '' then FirstName + ' ' + LastName else FirstName + ' ' + MiddleName + ' ' + LastName end as Name, ... from tbSystemUser