populating a column by calling a function

• rocky

  SSCrazy

  Points: 2120

  More actions

  December 5, 2006 at 9:17 am

  #61989

  Is there such a thing as calling a function to populate a column when adding a column using ALTER table?

  A developer proposed this but I've not heard of it.  It seems like it would be the functionality of an insert trigger, but he's saying it's a function.

  Anyone have an example?

  thanks!

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  December 5, 2006 at 9:25 am

  #675916

  I thaught about this but it's not accepted.

  ALTER TABLE tableName

  ADD Whatever INT CONSTRAINT D_CName DEFAULT (dbo.fn_name(ColName))

  How about adding the column, then running the update statement right after?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 5, 2006 at 9:36 am

  #675920

  i believe you can use a calculated column as well; here's an example that uses a function:

  CREATE FUNCTION GetAge  (@BirthDate datetime, @CurrentDate datetime)

  RETURNS int

  AS

  BEGIN

    return  year(@CurrentDate) - year(@BirthDate)

                  - case when month(@CurrentDate)

                 > month(@BirthDate)

                 then 0

                 else

                 case when month(@CurrentDate)

                 < month(@BirthDate)

                 then 1

                 else

                 case when day(@CurrentDate)

                 < day(@BirthDate)

                 then 1

                 else 0

                 end

                 end

                 end

  END

   

  create table age(

  ageid int identity(1,1) not null primary key,

  dob datetime,

  calcage as dbo.getage(dob,getdate() ) )

  or if i was adding this to an existing table:

  ALTER TABLE OLDAGE ADD calcage as dbo.getage(dob,getdate() )

  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!

• MohammedU

  SSCoach

  Points: 19644

  More actions

  December 5, 2006 at 10:48 am

  #675955

  If you post schema and explains your question based on the schema... Posters can post exact solution instead of guessing.

   

  MohammedU
  Microsoft SQL Server MVP