Post reply

Dynamic SQL to set column default value to Char(1)

  • October 24, 2007 at 11:06 am

    #179528

    Hi all,

    I have been battling this simple dynamic query for hours now! Can you look at the query below and figure out why I keep getting an error message when trying to create this column in table TEST. I believe the problem has to do with the quotes, but I cannot figure it out. I am using SQL Server 2005.

    Note: the script works if I set the default to an integer, but fails when I use "Y", with the message

    The name "Y" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

    set @sql =

    N'ALTER TABLE ' + @table_name + ' ADD "Testcolumn" char(1) not null default ("Y")'

    EXEC (@sql)

    Thanks!!

    Adam


    My blog: http://jahaines.blogspot.com

  • October 24, 2007 at 11:18 am

    #745051

    the Y should be surrounded with two single quotes, not a double quote....

    a double quote implies a column name:

    set @sql =

    N'ALTER TABLE ' + @table_name + ' ADD "Testcolumn" char(1) not null default (''Y'')'

    EXEC (@sql)

    set @sql =

    N'ALTER TABLE ' + @table_name + ' ADD "Testcolumn" char(1) not null default ([singlequote][singlequote]Y[singlequote][singlequote] '

    EXEC (@sql)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 24, 2007 at 11:18 am

    #745053

    Do you have quotes arround the 'Y' in the default, or two single ticks?

    When I copied the text out to a query window, it came across as quotes...it really should be two ticks (one real one, one escape) before and after the 'Y'.

    The Redneck DBA

  • October 24, 2007 at 11:25 am

    #745056

    Wow its that easy :blink: Live and learn.

    Thanks for your help Lowell, and Jason!


    My blog: http://jahaines.blogspot.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply