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.
>Solution :
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;