I am using a seperate table to hold all the localized value for each table and  lauguages. The table is something like:

LocaleID, SourceTableName, SourceField, SourcePKValue, Text

e.g.

'en-US',   'dbo.Author', 'Name','5','Rob'

In the stored procedure where localized value is reuqired to return, use the following function to retrive the value:

dbo.fn_GetLocalizedName (LocaleID,SourceTableName,SourceField,SourcePKValue)

This method can support different languages without changing the code.