הבלוג של גרי רשף

05/02/2011

החלפת מחרוזת אחת באחרת בתוך קוד פרוצדורה

Filed under: Uncategorized — תגיות: , , — גרי רשף @ 15:03

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

Create Proc MyProc As

Select    *

From    sys.objects

Where    name Like '%A%';

Go

פרוצדורה זו שולפת את כל האובייקטים שבשמם מופיעה האות A, ואנחנו רוצים שתשלוף את אלו שבשמם מופיעה האות B..

טוב, זו דוגמה קצת מטופשת, אבל נניח שבמציאות מדובר במשהו יותר מורכב, ונקבל כאקסיומה שהלקוח תמיד צודק ואם זה מה שהוא רוצה, מי אנו שנתווכח איתו? (טוב- אולי נתווכח בקטנה אבל משיעמוד על דעתו- נספק לו את מבוקשו)

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

Create Table #MyTbl(I Int Identity,S Varchar(Max));

Go


Insert Into #MyTbl

Exec SP_HelpText 'MyProc';

Go

כעת נכניס את קוד הפרוצדורה לתוך משתנה,

נשנה את Create Proc ל-Alter Proc (שינוי פרוצדורה קיימת),

את המחרוזת %A% נחליף ב-%B% (בזהירות- אם נשנה רק את A ל-B המילה השמורה As תהפוך ל-Bs ושם העמודה name ל-nBme..),

ונריץ את הקוד המתוקן:

 

Declare    @SQL Varchar(Max)


Select    @SQL=IsNull(@SQL,'')+S

From    #MyTbl

Order By I;


Set        @SQL=Replace(@SQL,'Create Proc','Alter Proc');

Set        @SQL=Replace(@SQL,'%A%','%B%');


Print @SQL;

Exec(@SQL);

Go

כעת צריך רק להריץ את הפרוצדורה MyProc או לעיין בקוד שלה בעזרת SP_HelpText כדי להשתכנע שהיא תוקנה.

מודעות פרסומת

2 תגובות »

  1. מה בדיוק לא עובד?
    אילו הודעות שגיאה או תוצאות שגויות את מקבל?

    נראה לי שיש לך הודעת שגיאה כי (DECLARE @SQL VARCHAR(MAX מופיע בתוך הלולאה והמערכת לא יכולה להגדיר אותו בכל פעם מחדש.

    אם זה לא מתבצע- נסה להפעיל את SP_HelpText לפני ואחרי העדכון כדי לראות את הקוד לפני ואחרי (הכוונה להפעיל מבלי להפנות לטבלה זמנית, רק כדי שתוכל לראות).

    תגובה של גרי רשף — 09/02/2011 @ 19:55

  2. הייי
    שאלתי אותך את זה בתפוז
    תודה על התשובה גם שפ וגם פה.

    GO
    /****** Object: StoredProcedure [dbo].[spSearchReplacer] Script Date: 02/09/2011 16:39:48 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[spSearchReplacer] (@isShowOnly BIT,
    @search NVARCHAR(1000),
    @replacer NVARCHAR(1000))
    AS
    BEGIN
    DECLARE @P1 VARCHAR(MAX),
    @P2 VARCHAR(MAX)
    DECLARE my_cur CURSOR FOR
    SELECT name,
    Object_definition(object_id)
    FROM sys.procedures
    WHERE Object_definition(object_id) LIKE '%' + @search + '%'
    AND name NOT LIKE 'sp_%'

    OPEN my_cur

    FETCH NEXT FROM my_cur INTO @P1, @P2

    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF Object_id ('tempdb..#MyTbl', 'u') IS NOT NULL
    DROP TABLE #mytbl;

    CREATE TABLE #mytbl
    (
    i INT IDENTITY,
    s VARCHAR(MAX)
    );

    INSERT INTO #mytbl
    EXEC Sp_helptext @P1;

    DECLARE @SQL VARCHAR(MAX)

    SELECT @SQL = Isnull(@SQL, ") + s
    FROM #mytbl
    ORDER BY i;

    SET @SQL=REPLACE(@SQL, 'Create Proc', 'Alter Proc');
    SET @SQL=REPLACE(@SQL, @search, @replacer);

    IF @isShowOnly = 1
    BEGIN
    SELECT @p1
    END
    ELSE
    BEGIN
    EXEC(@SQL);
    END

    FETCH NEXT FROM my_cur INTO @P1, @P2
    END

    CLOSE my_cur

    DEALLOCATE my_cur
    END

    אם כי הוא לא מחליף לי את התוו ]
    ואני יודע שצריך לשנות במקום ] ל []] אבל זה עדין לא עובד……
    בשאר זה כן עובד

    מה אתה אומר ?

    תגובה של rnan — 09/02/2011 @ 16:41


RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

%d בלוגרים אהבו את זה: