ALTER TABLE problem

Hiya all,

Im doing a system tool application. The app has the functionality to edit and change language strings that other applicaction uses.

The table look as following:

STRING_ID English Swedish
----------
100 Cancel Avbryt
101 Apply Verst'a'll

STRING_ID : Int, NOT NULL, Primary key
English : NVARCHAR(512), NOT NULL
Swedish : NVARCHAR(512), NOT NULL

In my tool you're able to add new strings.

Now I want to be able to add languages by adding new columns to the table.

Example:

STRING_ID English Swedish Arabic
------------
100 Cancel Avbryt <Cancel in arabic>
101 Apply Verst'a'll <Apply in arabic>

I've made a Stored Procedure looking like following:

CREATE PROC My_sp_AddNewLanguage
@.NewLanguageName nvarchar(512), @.RetrievalMsg nvarchar(255) OUTPUT
AS
-- Find out how many rows that should be affected when altering the table
DECLARE @.NrOfRows integer
SELECT * FROM String_Resource
SET @.NrOfRows = @.@.ROWCOUNT

ALTER TABLE String_Resource
ADD @.NewLanguageName NVARCHAR(512) NOT NULL
DEFAULT ('')
IF @.@.ROWCOUNT <> @.NrOfRows
BEGIN
SET @.RetrievalMsg = 'Unable to Add New Language'
RETURN 8301 -- 8301 is something I've defined in my code
END
SET @.RetrievalMsg = 'Your new Language has now been added'
RETURN 0
GO

But I cant seem to do this because of the @.NewLanguageName in the following row:

ALTER TABLE String_Resource
ADD @.NewLanguageName NVARCHAR(512) NOT NULL
DEFAULT ('')

So my question is: How can you add a column to the Table using a variable @.Variable that contains the name of the new column?

OR

Can anybody tell me how I can write DEFAULT('') into a NVARCHAR variable since in Store Procedure Strings are using the '-sign and the DEFAULT ('') expression has those signs in it.

I've tried doing @.SQLQuery = N'ALTER TABLE String_Resource ADD ' + @.NewLanguageName + ' NVARCHAR(512) NOT NULL DEFAULT('')'

But it doesnt work since the DEFAULT('') expression screws up the string.

Thanks for your time,
FarekYou've got a really good idea, but I think you are going about it all wrong!

Take a look at the master.dbo.sysmessages table. It is designed to do exactly what you are trying to do. The secret is to have multiple rows with the same message id, but only one row per language. By adding rows to the language table, you can then add new rows to the message table for that language, and you are on your way!

The syntax should go something like:CREATE TABLE tLanguage (
languageId INT IDENTITY
CONSTRAINT XPKtLanguage
PRIMARY KEY (languageId)
, name NVARCHAR(25) NOT NULL
)
GO

CREATE TABLE tMessage (
languageId INT NOT NULL
CONSTRAINT XFK01tMessage
FOREIGN KEY (languageId)
REFERENCES tLanguage (languageId)
, messageId INT NOT NULL
CONSTRAINT XPKtMessage
PRIMARY KEY (languageId, messageId)
, message NVARCHAR(50) NOT NULL
)
GO-PatPsql