שינוי פונקציה שנעשה בה שימוש בעמודה מחושבת

02/08/2012

יצרנו טבלה ובה עמודה מחושבת המציגה ערך של פונקציה, ואנחנו מעוניינים לשנות את הפונקציה.
לכאורה משימה טריוויאלית של Alter Function או לחילופין Drop & Create Function, אלא שמתברר שלא ניתן לעשות זאת במקרה זה.
דוגמה- ניצור פונקצייה סקלרית פשוטה המחזירה מחרוזת בת שישה תווים הכוללת את מספר החודש ומספר השנה:

Use tempdb;

Go

 

If Object_ID('MyFnc','FN') Is Not Null Drop Function MyFnc;

Go

 

Create Function dbo.MyFnc(@D As DateTime)

Returns Char(6)

As

Begin

Return (Right('0'+Cast(Month(@D) As VarChar),2)+Cast(Year(@D) As Char(4)));

End

Go

 

Select dbo.MyFnc(GetDate());

clip_image002

במקרה זה הפונקציה מחזירה 082012 (אוגוסט 2012 – חודש כתיבת פוסט זה).

כעת ניצור טבלה עם עמודת תאריכים ועמודה מחושבת העושה שימוש בפונקציה הנ"ל:

If Object_ID('MyTbl','U') Is Not Null Drop Table MyTbl;

Go

 

Create Table MyTbl(MyDate DateTime,

                   MyMonth As dbo.MyFnc(MyDate));

Go

 

Insert

Into   MyTbl

Select '20120101' Union All

Select '20120415' Union All

Select '20120731' Union All

Select GetDate();

 

Select * From MyTbl;

clip_image004

כעת אנחנו רוצים לשנות את הפונקציה: יותר הגיוני שהשנה תהיה משמאל והחודש מימין (למשל- 201208):

Alter Function dbo.MyFnc(@D As DateTime)

Returns Char(6)

As

Begin

Return (Cast(Year(@D) As Char(4))+Right('0'+Cast(Month(@D) As VarChar),2));

End

Go

clip_image006

לא ניתן לשנות מפני שנעשה בה שימוש בטבלה נ"ל, ובוודאי שאי אפשר לבצע Drop & Create.

למיטב ידיעתי אין לזה פתרון זולת הסרת העמודה המחושבת, תיקון הפונקציה, והוספת העמודה מחדש.

מכיוון שיתכן ונעשה שימוש בפונקציה בעוד עמודות מחושבות, נשתמש בטבלאות המערכת כדי למצוא את כל המופעים, וליצור להם פקודות הסרה והוספה מתאימות:

Select  'Alter Table ['+Object_Name(object_id)+'] Drop Column ['+name+'];' [Drop Column],

        'Alter Table ['+Object_Name(object_id)+'] Add ['+name+'] As '+definition+';' [Recreate Column],

        *

From    sys.computed_columns

Where   name='MyMonth'

        And object_id In (Select object_id From sys.tables);

clip_image008

וכעת נמקם את הפקודות שבעמודות Drop Column לפני פקודת שינוי הפונקציה,

ואת הפקודות שבעמודת Recreate Column אחריה:

Alter Table [MyTbl] Drop Column [MyMonth];

Go

 

Alter Function dbo.MyFnc(@D As DateTime)

Returns Char(6)

As

Begin

Return (Cast(Year(@D) As Char(4))+Right('0'+Cast(Month(@D) As VarChar),2));

End

Go

 

Alter Table [MyTbl] Add [MyMonth] As ([dbo].[MyFnc]([MyDate]));

Go

 

Select * From MyTbl;

clip_image010

הבעייה נפתרה.

ניתן לחילופין להיעזר בטבלאות מערכת אחרות כדי להגיע למידע המבוקש, אך עדיין אין ברירה אלא להסיר וליצור מחדש את העמודות:

Select  Object_Name(id),

        Object_Name(depid),

        *

From    sys.sysdepends

Where   Object_Name(depid)='MyFnc';

clip_image012

אם מישהו מכיר פתרון פשוט יותר- אשמח לשמוע.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

תגובה אחת

  1. גיא דורון31/05/2015 ב 09:41

    אני עוטף א הפונקציה בפונקציה נוספת, ואת הפונקציה הנוספת שם בעמודה המחושבת.
    לדוגמא: אם יצרתי הפונקציה הבאה:
    CREATE FUNCTION example (@int1 int, @int2 int)
    RETURNS int
    AS
    BEGIN
    DECLARE @Result int = @int1+@int2
    RETURN @Result
    END
    GO
    שעליה אני רוצה לבסס עמודה מחושבת, אני יוצר פונקציה נוספת עם אותם ארגומנטים שקוראת לה:
    CREATE FUNCTION example_1 (@int1 int, @int2 int)
    RETURNS int
    AS
    BEGIN
    DECLARE @Result int = example(@int1 , @int2)
    RETURN @Result
    END
    GO
    את הפונקציה השניה אני שם בעמודה המחושבת. במצב כזה ניתן לערוך את הפונקציה המקורית מבלי שהשרת יצעק

    הגב