Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003

    Question Unanswered: 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.

    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
    -- 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
    	SET @RetrievalMsg = 'Unable to Add New Language'
    	RETURN 8301  -- 8301 is something I've defined in my code
    SET @RetrievalMsg = 'Your new Language has now been added'
    RETURN 0
    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?


    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,

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    You'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
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts