Azure SQL存储过程用变量创建数据库范围凭据报错求助

问题原因 #

CREATE DATABASE SCOPED CREDENTIAL语句的IDENTITY和SECRET参数不支持直接使用存储过程的局部变量，直接引用变量会触发语法错误，必须通过动态SQL实现参数传递。

解决方案 #

使用sp_executesql执行参数化动态SQL，既满足语法要求，又能避免SQL注入风险，具体代码如下：

CREATE PROCEDURE [dbo].[sp_someTask](@username nvarchar(20), @password nvarchar(50))
AS
BEGIN
    SET NOCOUNT ON;

    -- 定义带参数占位符的动态SQL，先检查凭据是否已存在
    DECLARE @dynamicSql NVARCHAR(MAX) = N'
        IF NOT EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name = ''MyCredential'')
        BEGIN
            CREATE DATABASE SCOPED CREDENTIAL MyCredential
            WITH IDENTITY = @Identity,
                 SECRET = @Secret;
        END';

    -- 执行动态SQL并传入参数
    EXEC sp_executesql 
        @statement = @dynamicSql,
        @params = N'@Identity NVARCHAR(20), @Secret NVARCHAR(50)',
        @Identity = @username,
        @Secret = @password;
END;

关键说明 #

1. 参数化传递：通过sp_executesql传递参数，而非直接拼接字符串，既能解决变量引用的语法问题，又能有效防范SQL注入，保障凭据信息安全。
2. 存在性检查：加入IF NOT EXISTS判断，避免重复创建同名凭据导致报错，提升存储过程的健壮性。

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