לשולחן המערכת הגיעה השאלה הבאה: יש צורך בסקריפט שיאפשר לתקן פרוצדורות כך שמחרוזת המופיעה בהן תוחלף באחרת.
למשל:
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 כדי להשתכנע שהיא תוקנה.
מה בדיוק לא עובד?
אילו הודעות שגיאה או תוצאות שגויות את מקבל?
נראה לי שיש לך הודעת שגיאה כי (DECLARE @SQL VARCHAR(MAX מופיע בתוך הלולאה והמערכת לא יכולה להגדיר אותו בכל פעם מחדש.
אם זה לא מתבצע- נסה להפעיל את SP_HelpText לפני ואחרי העדכון כדי לראות את הקוד לפני ואחרי (הכוונה להפעיל מבלי להפנות לטבלה זמנית, רק כדי שתוכל לראות).
תגובה מאת גרי רשף — 09/02/2011 @ 19:55
הייי
שאלתי אותך את זה בתפוז
תודה על התשובה גם שפ וגם פה.
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