locked
Changed a computer column function RRS feed

  • Question

  • I had an issue that I ran across that I got solved but it was very monotonous. Here is my issue:

    I have an SQL function that takes in 2 parameters (lookup category and lookup code) and with those parameters is goes out and find the lookup code description. I have this function in about 40 computed columns. It isn’t the best way to do things but we need it for some legacy stuff. The problem occurs when I need to make a change to that function. I can’t because it is being reference by all of the tables. So what I have to do is go in to each of the tables and remove the function from the column formula. Then change the function and then finally add that function back to the column.

     

    Here is what I did.

    I went to the syscolumn table to find all of the computer columns. I went to each of those columns and removed the formula and added it to a text document. Then I changed the function. I then wrote a stored procedure that would add the column back. ( I couldn't find a way to use the ALTER COLUMN to add the function so I had to drop and then add the column )

     

    Here are the possible scenarios that I want to see if they are possible.

    1) Somehow “turn off” the dependency between the function and the column. That way I can just update the function.

    OR

    2) Programmatically grab the data from the column formula and write that some place and clear out the formula. Then update the function. Then finally take all of the data that was cleared out and write it back to the column.

     

    Does anyone know how to make this easier?

    Friday, October 12, 2007 2:19 PM

Answers

  • How about a wrapper function?

     

    Have the column computed on funcA and then just let funcA return the values of funcB (the function that requires change). The dependancy is then just on funcA allowing you free reign to change funcB.


    HTH!

    Friday, October 12, 2007 2:33 PM

All replies

  • How about a wrapper function?

     

    Have the column computed on funcA and then just let funcA return the values of funcB (the function that requires change). The dependancy is then just on funcA allowing you free reign to change funcB.


    HTH!

    Friday, October 12, 2007 2:33 PM
  • I can't beleive that solution works. Although I would have still had to do the same process that I mentioned because I had to change the size of the parameter.

    Thanks for the help.

    Friday, October 12, 2007 8:05 PM