我们平常得到的脚本往往含有GO, 这是无法用动态语句直接执行的, 但有时GO不能直接去掉(比如create view之类), 如何处理?
1. 需要按行分割表值函数:Fun_SplitByLine
2. 增加一个存储即可:
IF OBJECT_ID('dbo.Proc_DBA_ExecBySplitGo') IS NOT NULL
DROP PROC dbo.Proc_DBA_ExecBySplitGo
GO
-- =============================================
-- Author: yenange
-- Create date: 2017-12-19
-- Description: 将一个脚本字符串按GO分割后执行
-- =============================================
CREATE PROCEDURE dbo.Proc_DBA_ExecBySplitGo
@sql NVARCHAR(MAX)='select 1 as r
go
select 2 as r
go '
AS
BEGIN
SET NOCOUNT ON;
IF ISNULL(@sql,'')=''
BEGIN
RAISERROR ('SQL脚本字符串为空!',16,1)
RETURN;
END
--1. 将语句分割
DECLARE @t TABLE(
rowNum INT,
line NVARCHAR(MAX)
)
INSERT INTO @t(rowNum,line)
SELECT rowNum,line FROM dbo.Fun_SplitByLine(@sql)
DECLARE @tempSql NVARCHAR(MAX),@i INT,@iMax INT,@line NVARCHAR(MAX),@times INT
SELECT @i=1,@iMax=MAX(rowNum),@tempSql='',@times=1 FROM @t
WHILE @i<=@iMax
BEGIN
SELECT @line=line FROM @t WHERE rowNum=@i
IF ltrim(rtrim(@line))='GO' OR @i=@iMax
BEGIN
PRINT ''
PRINT '----'+CAST(@times AS VARCHAR(50))+'. 分割执行'
PRINT @tempSql
EXEC (@tempSql)
SET @tempSql=''
SET @times=@times+1
END
ELSE
BEGIN
SET @tempSql=CASE WHEN @tempSql='' THEN @line ELSE @tempSql+'
'+@line END
END
SET @i=@i+1
END
END
GO