locked
Altering a calculated column in run-time? RRS feed

  • Question

  • Hi all of you,

    RDBMS is 2005 Express Edition.

    This is the script for my affected table:

    CREATE TABLE [dbo].[Talleres_OR](

         [Ord_rep] [int] IDENTITY(1,1) NOT NULL,

         [ord_repalpha]  AS ('OR0'+CONVERT([varchar](10),[ord_rep],(0))),

          [F_Alta] [datetime] NOT NULL,

          [F_Cierre] [datetime] NULL,

          [Matricula] [varchar](10) COLLATE Modern_Spanish_CI_AS NULL,

          [Acciones] [text] COLLATE Modern_Spanish_CI_AS NULL,

          [Usuario] [varchar](8) COLLATE Modern_Spanish_CI_AS NULL,

     CONSTRAINT [PK_Talleres_OR] PRIMARY KEY CLUSTERED

    (

          [Ord_rep] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

     

     

    As you see 'ord_repalpha' field is a calculated column depending on an identity field (ord_rep). I have got one stored procedure which inserts a row in the table. But now the business rules has been changed and I want ad-hoc change this rule allowing differents strings: OR, RO, TO, and so on and forth.

    I have not any idea how... The table is populated with thousands of rows and if I change and put directly this I will change all the rows...

     

    [ord_repalpha]  AS ('R0'+CONVERT([varchar](10),[ord_rep],(0))),

     

     

    I want to change just for a concrete case

          [ord_repalpha]  AS ('OR0'+CONVERT([varchar](10),[ord_rep],(0))),

     

    to

          [ord_repalpha]  AS ('R0'+CONVERT([varchar](10),[ord_rep],(0))),

     

     

    I am totally stuck with that. I could change all the rows to 'RO' beginning and then make an UPDATE in order to leave the table as before except the affected one but it seems to me a total mess, bad practice...

     Thanks for your comments and advices,

     


    enr

    Monday, May 14, 2012 10:10 AM

Answers

  • 
    

    Indeed, there is no other way to modify your computed column other than drop it.

    However, as your computed column is not persisted so the impact is very small. you can use this to drop your column:

    Alter TABLE [dbo].[Talleres_OR] drop column [ord_replpha];

    after you dropped the column you can add it back with:

    alter table [dbo].talleres_or] add [ord_repalpha]  AS ('R0'+CONVERT([varchar](10),[ord_rep],(0)));

    • Marked as answer by Enric Vives Monday, May 14, 2012 10:33 AM
    Monday, May 14, 2012 10:27 AM
  • you can create a new column like 'Dynamic_Value' whatever suits you. You use this column to hold your change value" 'RO', 'OT', 'TO' etc. (or you can simply update the column dynamically'

    Then create your computed column as

    alter table [dbo].talleres_or] add [ord_repalpha]  AS ([Dynamic_value]+CONVERT([varchar](10),[ord_rep],(0)));

    by doing so, your comupted column value can be updated through the dynamic_value column.

    I hope this works for you.

    • Marked as answer by Enric Vives Monday, May 14, 2012 10:53 AM
    Monday, May 14, 2012 10:46 AM

All replies

  • 
    

    Indeed, there is no other way to modify your computed column other than drop it.

    However, as your computed column is not persisted so the impact is very small. you can use this to drop your column:

    Alter TABLE [dbo].[Talleres_OR] drop column [ord_replpha];

    after you dropped the column you can add it back with:

    alter table [dbo].talleres_or] add [ord_repalpha]  AS ('R0'+CONVERT([varchar](10),[ord_rep],(0)));

    • Marked as answer by Enric Vives Monday, May 14, 2012 10:33 AM
    Monday, May 14, 2012 10:27 AM
  • Hi Steven, thanks for your responde.

    But doing that all the rows will have 'RO' as a value. 'suppose your proposal is after the second ALTER does the INSERT and then does DROP 'RO' and finally create again 'OR'...

    I know it is a little bit confused


    enr

    Monday, May 14, 2012 10:33 AM
  • you can create a new column like 'Dynamic_Value' whatever suits you. You use this column to hold your change value" 'RO', 'OT', 'TO' etc. (or you can simply update the column dynamically'

    Then create your computed column as

    alter table [dbo].talleres_or] add [ord_repalpha]  AS ([Dynamic_value]+CONVERT([varchar](10),[ord_rep],(0)));

    by doing so, your comupted column value can be updated through the dynamic_value column.

    I hope this works for you.

    • Marked as answer by Enric Vives Monday, May 14, 2012 10:53 AM
    Monday, May 14, 2012 10:46 AM
  • Steven, yep now I have got it!!

    very kind of you, regards


    enr

    Monday, May 14, 2012 10:53 AM