SQL-SERVER: how to get the maximum length of a variable of VARCHAR help
You are comparing integer to string. You need to compare length of customer name with max of length and not with direct customer name.

You could modify your code as below:

SELECT
CustomerName AS LongestName,
LEN(CustomerName) AS LengthOfName,
FROM
MsCustomer
where 
len(CustomerName) = (SELECT MAX(LEN(CustomerName)) from MsCustomer)


I am not really sure what are you trying to do with STATUS = 'Customer' code.

There are tonnes of article on web for finding the record for max of certain things.

Hope this answers your concern.