随笔- 672 文章- 2 评论- 325 阅读- 281万

SQL Server使用sp_executesql在存储过程中执行多个批处理

SQL Server中有些SQL语句只能在一个批处理里面完成，例如CREATE SCHEMA语句创建SCHEMA的时候，每个SCHEMA都需要在一个单独的批处理里面完成：

CREATE SCHEMA [raw];
GO

CREATE SCHEMA [src];
GO

CREATE SCHEMA [app];
GO

所以上面语句中，我们就要在每个CREATE SCHEMA语句后面加上GO关键字，使得每个CREATE SCHEMA语句在单独的批处理中执行，这样三个CREATE SCHEMA语句才不会报错。

但是如果我们想把这三个CREATE SCHEMA语句直接封装在一个存储过程中，又不行了，因为存储过程中是不能够有GO语句的，例如如果我们创建个存储过程dbo.SP_CreateSchemas，将三个CREATE SCHEMA语句直接放进去，如下所示：

CREATE PROCEDURE dbo.SP_CreateSchemas
AS
BEGIN
    
    CREATE SCHEMA [raw];
    GO

    CREATE SCHEMA [src];
    GO

    CREATE SCHEMA [app];
    GO

END
GO

执行上面的创建存储过程时，SQL Server会报语法错误，错误提示如下：

Msg 156, Level 15, State 1, Procedure SP_CreateSchemas, Line 5 [Batch Start Line 0]
Incorrect syntax near the keyword 'SCHEMA'.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'END'.

原因就是在存储过程中，是不能有GO关键字的。

那么怎么才能在SQL Server的存储过程中，执行多个批处理才能执行的语句呢？

这时候我们可以用到sp_executesql存储过程，sp_executesql这个存储过程我相信熟悉SQL Server的开发人员都不会陌生，它可以用来执行由字符串拼接而成的SQL语句。而sp_executesql存储过程执行SQL语句的时候，还有个特点，那就是sp_executesql存储过程会将字符串拼接而成的SQL语句在一个单独的批处理中执行。

所以本例中如果我们要在一个存储过程中执行三个CREATE SCHEMA语句，可以通过调用三次sp_executesql存储过程来实现，我们将上面dbo.SP_CreateSchemas存储过程的代码改为如下:

CREATE PROCEDURE [dbo].[SP_CreateSchemas]
AS
BEGIN
    
    DECLARE @sql NVARCHAR(MAX);

    SET @sql=N'
    CREATE SCHEMA [raw];
    '
    EXEC sp_executesql @sql;

    SET @sql=N'
    CREATE SCHEMA [src];
    '
    EXEC sp_executesql @sql;

    SET @sql=N'
    CREATE SCHEMA [app];
    '
    EXEC sp_executesql @sql;

END
GO

由于SQL Server的存储过程中是可以多次调用其它存储过程的，所以我们在dbo.SP_CreateSchemas存储过程中，通过调用三次sp_executesql存储过程，每次执行一个CREATE SCHEMA语句，这样相当于就是使用了三个批处理分别执行了三个CREATE SCHEMA语句。这里顺便说一下，sp_executesql存储过程执行字符串SQL语句时，也不能在字符串SQL语句中使用GO关键字，只能够通过多次调用sp_executesql存储过程来代替GO关键字。

执行dbo.SP_CreateSchemas存储过程，现在三个SCHEMA就在数据库中成功创建好了：