Set language for the session dynamically

Question

I am having trouble parameterizing the language variable in a stored procedure. I am writing a procedure to create date dimension table and would like to have a "language" parameter.

According to the documentation, this should be possible by creating a variable of type sysname.

However, executing the following code results in an error:

DECLARE @Language sysname;
SET @Language = 'Spanish';
SET LANGUAGE @Language;

Parse error at line: 1, column: 20: Incorrect syntax near 'N'Spanish''.

I noticed that I can execute the command with dynamic SQL, although this doesn't actually change the language which seems extra strange.

For example:

SET LANGUAGE English

EXEC('SET LANGUAGE Spanish') -- this doesn't work
SELECT DATENAME(MONTH, GETDATE()) AS MonthName

SET LANGUAGE Spanish -- this works
SELECT DATENAME(MONTH, GETDATE()) AS MonthName

Returns:

MonthName
July

MonthName
Julio

Any ideas?