存储过程应用一个例子

--注意：脚本只能执行一次，不能执行多次
IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL   
    DROP FUNCTION dbo.RegexReplace  
GO  
CREATE FUNCTION dbo.RegexReplace  
(  
    @string VARCHAR(MAX),   --被替换的字符串  
    @pattern VARCHAR(255),  --替换模板  
    @replacestr VARCHAR(255),   --替换后的字符串  
    @IgnoreCase INT = 0 --0区分大小写 1不区分大小写  
)  
RETURNS VARCHAR(8000)  
AS   
BEGIN  
    DECLARE @objRegex INT, @retstr VARCHAR(8000)  
    --创建对象  
    EXEC sp_OACreate 'VBScript.RegExp', @objRegex OUT  
    --设置属性  
    EXEC sp_OASetProperty @objRegex, 'Pattern', @pattern  
    EXEC sp_OASetProperty @objRegex, 'IgnoreCase', @IgnoreCase  
    EXEC sp_OASetProperty @objRegex, 'Global', 1  
    --执行  
    EXEC sp_OAMethod @objRegex, 'Replace', @retstr OUT, @string, @replacestr  
    --释放  
    EXECUTE sp_OADestroy @objRegex  
    RETURN @retstr  
END  
GO 

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

IF OBJECT_ID ( 'tester2', 'P' ) IS NOT NULL 
    DROP PROCEDURE tester2;
GO
CREATE PROCEDURE tester2
AS
BEGIN
	DECLARE Employee_Cursor CURSOR FOR
		SELECT tableName FROM MD_TreeTableStruct WHERE Type = '1';
	DECLARE @tableName1 varchar(50);
	OPEN Employee_Cursor;	
	FETCH NEXT FROM Employee_Cursor INTO @tableName1;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		print @tableName1;
	  exec('update ' + @tableName1 + ' set innerCode = dbo.RegexReplace (innercode, ''([0-9][0-9][0-9])'',''00$1'',0)');
      FETCH NEXT FROM Employee_Cursor INTO @tableName1;
	END;
	CLOSE Employee_Cursor;
	DEALLOCATE Employee_Cursor;	    
END
GO

exec tester2;
GO