SQL Server如何在动态指定名称的数据库中创建存储过程

SQL Server原生的CREATE/ALTER PROCEDURE语法规则明确禁止在存储过程对象名前添加数据库名称前缀，这就是触发166号报错的直接原因。

要在动态名称的数据库内创建存储过程，不需要在对象名前拼接库名，只需要在动态SQL的最开头添加上下文切换语句，将执行上下文切换到目标数据库后，再执行存储过程创建语句即可，创建的对象会自动归属到当前上下文对应的数据库下。

完整可运行的参考代码如下：

DECLARE @DB_NAME sysname
DECLARE @SqlCommand nvarchar(max)
-- 读取目标数据库名称
SELECT @DB_NAME = valeur FROM #vars WHERE nom = 'DB_NAME'

-- 前置校验，避免空值导致执行异常
IF @DB_NAME IS NULL
BEGIN
    RAISERROR('无法从临时表获取目标数据库名称', 16, 1)
    RETURN
END

-- 拼接动态执行语句
SET @SqlCommand = N'
-- 切换上下文到目标数据库
USE ' + QUOTENAME(@DB_NAME) + N';

-- 创建存储过程，无需添加库名前缀
CREATE OR ALTER PROCEDURE [dbo].[sp_DEFRAG_Index]
    @Pourcent int = 30
AS
BEGIN
    SET NOCOUNT ON;
    -- 此处填写存储过程的具体业务逻辑，例如索引碎片整理逻辑
END
'

-- 执行动态SQL
EXEC sp_executesql @SqlCommand

• 必须使用QUOTENAME()函数包裹动态获取的数据库名，既可以避免库名包含空格、特殊字符、系统保留字时触发语法错误，也能防范SQL注入风险。
• 执行脚本前请确认目标数据库已经创建完成，且执行账号拥有目标数据库的CREATE PROCEDURE权限。
• 代码中使用的CREATE OR ALTER PROCEDURE语法支持SQL Server 2016 SP1及以上版本，如果是更低版本，可以替换为「判断存储过程存在则先删除，再创建」的逻辑。

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