none
Steps to alter function used as computed column

    Question

  •  

    What is the best way to change a function that is currently being referenced from a calculated column in a table?  I get the following error when I try to alter it.

     

    Msg 3729, Level 16, State 3, Procedure fn_sdtAcctJoin2, Line 232

    Cannot ALTER 'dbo.fn_sdtAcctJoin2' because it is being referenced by object 'tblMembers'.

    Thursday, April 19, 2007 2:56 PM

Answers

  • 1. Remove the computed column using


    ALTER TABLE MyTable
       DROP COLUMN MyComputedColumn

    2. Correct the FUNCTION

     

    3. Recreate the computed column using:

      
       ALTER TABLE MyTable
          ADD COLUMN MyComputedColumn = NewDefinition

    Thursday, April 19, 2007 3:25 PM
    Moderator

All replies

  • 1. Remove the computed column using


    ALTER TABLE MyTable
       DROP COLUMN MyComputedColumn

    2. Correct the FUNCTION

     

    3. Recreate the computed column using:

      
       ALTER TABLE MyTable
          ADD COLUMN MyComputedColumn = NewDefinition

    Thursday, April 19, 2007 3:25 PM
    Moderator
  • I was hoping there was a better way of doing this.  Thanks for you help.
    Thursday, April 19, 2007 3:47 PM
  • I know this is an old question, but I just had the same problem.  The solution I came up with was:

     

    - create a new temporary function with the new code

    - alter table to use the temporary function

    - update the original function to the new code

    - alter table back to the original function

    - drop the temporary function

    Monday, November 10, 2008 6:57 PM
  • Another user on here, @epnlarry offered a workaround that involved calling a 'parent-function' which itself calls the function with all the logic in it.  In this way, the dependency is on this calling function that has no logic itself other than to call the second one.  When set up in this way, you'll be able to alter the important function all day long without any dependency issues.
    Tim Carper
    Friday, September 17, 2010 3:02 PM
  • It is because this function is used for a computed column while defining table and while it is in use you cant Alter the function which in use.
    I think you can solve this problem by following steps
    1) Go to the table where this function is used
    2) Right click n go to Modify  
    3) Select computed column
    4) at below in Column Properties select Computed Column Specification
    5) Clear formula box (its showing function name)
    6) Alter your function
    I hope it will help you.
    Thank you
    Monday, June 13, 2011 2:52 PM
  • Can someone please explain to me why it is so hard to alter a function that is used on a computed column?

    It would be like changing a function in C# BUT before you can change it you have to comment every usage out. What a nightmare this would be.

    Why is it this way?

     

    Thanks

     

    Thursday, August 18, 2011 2:02 PM
  • the best thing is to delete the computed column from the table, Alter the function, then add the column as a computed column.
    Tuesday, January 31, 2012 9:09 PM
  • I whole-heartily agree. The dependency check that is being done here in SQL Server seems absolutely unnecessary. I can understand if the signature changes it should complain, or if you are persisting the value (which you can't with a function) but to completely prevent changes just makes us go through additional hoops to update the code. While using a parent function is a workaround, it's not ideal in any way to do this as a practice across a whole database - unless you only use this functionality as a one-off.

    Dear Microsoft - please remove this limitation so I don't have to drop the column every time. Also, let me persist the data and offer a command to update the output when the function is changed. I'm going to hold my breath until you do this.


    Michael Brown, 360 Replays Ltd. (don't forget that 'Mark As Answer' button!)

    Tuesday, December 18, 2012 10:42 PM