How create stored procedure in specific database-CodePudding

I want create stored procedure when i create my database with variable name.

For create my database, at the top of the script I write USE [master] GO.

After I want to create my stored procedure in my new database but I can't specify the database in my query like this:

DECLARE @DB_NAME sysname
select @DB_NAME = valeur from #vars WHERE nom = 'DB_NAME'   /*Nom de la base de données*/

SET @SqlCommand = 'CREATE PROCEDURE '  @DB_NAME  '.[dbo].[sp_DEFRAG_Index]

@Pourcent int  = 30.0

AS BEGIN

Error :

Msg 166, Level 15, Status 1, Line 317

'CREATE/ALTER PROCEDURE' does not allow specifying the database name as a prefix to the object name.

CodePudding user response：

You can call sp_executesql dynamically, that is: you can create a value containing 'DB_NAME.sys.sp_executesql then call EXEC @name @params

DECLARE @DB_NAME sysname;
select @DB_NAME = valeur
from #vars
WHERE nom = 'DB_NAME';   /*Nom de la base de données*/

DECLARE @SqlCommand nvarchar(max) = '
CREATE PROCEDURE [dbo].[sp_DEFRAG_Index]

@Pourcent int  = 30.0

AS
';

DECLARE @spexecute nvarchar(1000) = QUOTENAME(@DB_NAME)   '.sys.sp_executesql';

EXEC @spexecute @SqlCommand;