MS SQL Server: Dynamical create procedure / function / view / trigger in other database (sp_executesql)

Question

Problem:

1. you want / need to use dynamic SQL to create procedure / function / view / trigger in other databases than the current
2. you can't specify the database name in the statement, CREATE VIEW tempdb.dbo.v_test AS SELECT 1; will fail with Error 166: 'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.
3. the CREATE (or ALTER) statement needs to be the first in the batch, otherwise you'll get the error 111: 'CREATE ...' must be the first statement in a query batch.. For This reason you can't simply write USE <database> before the CREATE:

DECLARE @create_sql NVARCHAR(max);
SET @create_sql = CONCAT('USE tempdb', CHAR(13) + CHAR(10),
 'CREATE VIEW dbo.v_test AS SELECT 1 AS n;'
 );
EXEC (@create_sql) -- fails
-- or
EXEC sys.sp_executesql @create_sql -- fails too

4. you can't use GO inside an EXEC() or EXEC sys.sp_executesql
   statement, because it is not in the SQL standard (just a configurable dummy word used inside SSMS and several other SQL tools), so you can't simply add it between the USE and the CREATE command in the script above as you would do it in SSMS when creating a "normal" deployment script
5. the internal procedure sys.sp_executesql exists in every database and executes the given command in its own database, but there may be many (e.g. hundred) databases, so that you can't really use multiple IF statements to "hardcode" where you want to execute it

• Thomas Franz: I don't know, which / how many databases are needed, so I can't hardcode the EXEC SomeOtherDatabase, because it could be any (even a database that a coworker created 5 minutes ago)

Answer 1

Solution:

You have to use a three times nested call.

1. you call sys.sp_executesql in the current database (e.g. master)
2. this calls sys.sp_executesql in the destination database (e.g. tempdb)
3. this finally calls an EXEC (@create_sql) to create the procedure / trigger / function / trigger etc.

Since the CREATE statement is passed throgh as variable and not as string, you don't have to worry about quotes, special chars or sql injection - except for the initial CREATE (@create_sql in the example) of course

Example:

USE master;
DECLARE @dest_db_name sysname = 'tempdb'
 , @create_sql NVARCHAR(max) = 'CREATE VIEW dbo.v_test AS SELECT 1 AS n;'
 , @dummy_cmd NVARCHAR(500)
;
 -- second exec -- third exec
SET @dummy_cmd = CONCAT('EXEC ', QUOTENAME(@dest_db_name), '.sys.sp_executesql N''EXEC (@create_sql)'', N''@create_sql NVARCHAR(MAX)'', @create_sql = @create_sql;')
 -- first exec 
EXEC sys.sp_executesql @dummy_cmd, N'@create_sql NVARCHAR(MAX)', @create_sql = @create_sql
-- control
SELECT * FROM tempdb.dbo.v_test
-- DROP can be used with other statements in the same batch, so it is much easier and doesn't need special treatment
SET @dummy_cmd = 'USE tempdb;' + CHAR(13) + CHAR(10) + 'DROP VIEW IF EXISTS dbo.v_test;'
EXEC (@dummy_cmd)