无法通过Azure Pipeline创建存储过程，请求排查语法错误

错误原因 #

报错的核心是动态SQL中的单引号转义处理错误，导致SQL Server解析时无法正确识别字符串边界，将%符号误判为语法元素而非字符串的一部分：

• 若误将转义单引号的双单引号写法写成了双引号，在SET QUOTED_IDENTIFIER ON的设置下，双引号包裹的内容会被当作数据库标识符（如表名、列名），而标识符不允许以%开头，因此触发语法错误。
• 若单引号数量不正确（如少写一个），会导致字符串提前闭合，后续的%符号脱离字符串上下文，被视为非法语法。

补充说明：你使用动态SQL创建存储过程的逻辑是正确的，因为CREATE PROCEDURE必须作为批处理的第一条语句，无法直接嵌套在IF NOT EXISTS的代码块中。

修正后的脚本 #

方法1：修正动态SQL的单引号转义 #

IF NOT EXISTS (SELECT ROUTINE_NAME 
               FROM INFORMATION_SCHEMA.ROUTINES 
               WHERE ROUTINE_NAME = 'sp_checknpapermission' 
                 AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
    SET QUOTED_IDENTIFIER ON

    EXEC(' 
          CREATE PROCEDURE sp_checknpapermission
          AS
          BEGIN
              SET NOCOUNT ON;
              DECLARE @UserName NVARCHAR(128);
              DECLARE user_cursor CURSOR FOR
                SELECT name
                FROM sys.database_principals
                WHERE name LIKE ''%@web.onmicrosoft.com'' AND type_desc = ''EXTERNAL_USER'';

              BEGIN TRY
                OPEN user_cursor;
                FETCH NEXT FROM user_cursor INTO @UserName;

                WHILE @@FETCH_STATUS = 0
                BEGIN
                IF NOT EXISTS (
                   SELECT 1
                   FROM sys.database_role_members AS rm
                   JOIN sys.database_principals AS r ON rm.role_principal_id = r.principal_id
                   JOIN sys.database_principals AS u ON rm.member_principal_id = u.principal_id
                   WHERE u.name = @UserName  AND r.name = ''db_owner''
                )
                BEGIN
                     PRINT ''The user '' + @UserName + '' does not have db_owner role'';
                     END
                     ELSE
                     BEGIN
                        PRINT ''The user '' + @UserName + '' already has the db_owner role'';
                     END
                     FETCH NEXT FROM user_cursor INTO @UserName;
                END

               CLOSE user_cursor;
               DEALLOCATE user_cursor;
             END TRY
             BEGIN CATCH
                 -- 注意：原脚本无对应事务开启逻辑，ROLLBACK会触发运行时错误，建议移除或补充事务代码
                 -- ROLLBACK;
                 PRINT ERROR_MESSAGE();
            END CATCH;
    END
  ')
END

方法2：拆分批处理避免动态SQL #

使用GO命令拆分批处理，直接创建存储过程，无需动态SQL：

IF EXISTS (SELECT ROUTINE_NAME 
           FROM INFORMATION_SCHEMA.ROUTINES 
           WHERE ROUTINE_NAME = 'sp_checknpapermission' 
             AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
    DROP PROCEDURE sp_checknpapermission;
END
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE sp_checknpapermission
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @UserName NVARCHAR(128);
    DECLARE user_cursor CURSOR FOR
      SELECT name
      FROM sys.database_principals
      WHERE name LIKE '%@web.onmicrosoft.com' AND type_desc = 'EXTERNAL_USER';

    BEGIN TRY
      OPEN user_cursor;
      FETCH NEXT FROM user_cursor INTO @UserName;

      WHILE @@FETCH_STATUS = 0
      BEGIN
      IF NOT EXISTS (
         SELECT 1
         FROM sys.database_role_members AS rm
         JOIN sys.database_principals AS r ON rm.role_principal_id = r.principal_id
         JOIN sys.database_principals AS u ON rm.member_principal_id = u.principal_id
         WHERE u.name = @UserName  AND r.name = 'db_owner'
      )
      BEGIN
           PRINT 'The user ' + @UserName + ' does not have db_owner role';
           END
           ELSE
           BEGIN
              PRINT 'The user ' + @UserName + ' already has the db_owner role';
           END
           FETCH NEXT FROM user_cursor INTO @UserName;
      END

     CLOSE user_cursor;
     DEALLOCATE user_cursor;
   END TRY
   BEGIN CATCH
       -- ROLLBACK;
       PRINT ERROR_MESSAGE();
  END CATCH;
END
GO

内容的提问来源于stack exchange，提问作者ramesh reddy