User Defined Function Return value
I can't say that I understand what it is that You are trying to do...
The thing is, just like You have already been told, that You can't use Dynamic SQL in a Function.  

Using a Stored Procedure will solve the problem You encountered:
(I did not try to find another logic to retrieving the same data, which is possible, I just converted Your Function to a Stored Procedure)  

CREATE PROC [dbo].[usp_getContentURL] 
(@VID INT, @accountID INT) 
AS 
DECLARE @cTable varchar(50) = '[CONTENT_CONNECT].[dbo].CONTENT_' + CONVERT(varchar(5),@accountID)
DECLARE @vTable varchar(50) = '[CONTENT_CONNECT].[dbo].VERSION_' + CONVERT(varchar(5),@accountID)
DECLARE @sql VARCHAR(max) =  '' 
SET @sql = ' 
DECLARE @CompanyID int
DECLARE @ProjectNumber int
DECLARE @ContentID int
DECLARE @VersionNumber int
DECLARE @FileName varchar(400)
select @VersionNumber=V.Number, @ContentID=V.ContentID, @ProjectNumber=P.Number, @FileName=C.Name, @CompanyID=P.CompanyID
from ' + @vTable + ' V
inner join' + @cTable + ' C on V.ContentID=C.ID
inner join Project P on C.ProjectID=P.ID 
where V.VID='+convert(varchar,@VID)+' 
select ''/''+Convert(varchar,@CompanyID)
+''/''+Convert(varchar,@ProjectNumber)
+''/''+Convert(varchar,@ContentID)
+''/''+Convert(varchar,@VersionNumber)
+''/''+@FileName'

EXEC (@sql)
GO


If this does not meet Your needs please post what exactly You are trying to do.
Good Luck,
Roi