SqlServer output and return value in procedure
It looks like you are attempting to give the value of @name back to the caller, but that is not the purpose of RETURN. When you give an argument after RETURN it must be an integer which indicates the status of the procedure.

Better off declaring @name as an output parameter or SELECT @name.

Ref. https://msdn.microsoft.com/en-us/library/ms174998.aspx