0

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
  1. 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
  2. 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
2
  • 1
    You forgot to ask a question but I think you want EXEC SomeOtherDatabase.sys.sp_executesql @create_sql;
    – Dan Guzman
    Jan 25, 2023 at 17:53
  • @DanGuzman: you forgot point 5 where you don't know, which / how many databases are needed, so you can't hardcode the EXEC SomeOtherDatabase, because it could be any (even a database, that your coworker created 5 minutes ago) Jan 26, 2023 at 13:12

1 Answer 1

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)
6
  • 1
    Valiant effort, but I'd personally prefer just calling SomeOtherDatabase.sys.sp_executesql like Dan suggesed. Nesting past level 2 can get confusing quick.
    – J.D.
    Jan 25, 2023 at 18:17
  • I agree, but as I just wrote to Dan, he forgot point 5 - you don't know on which database it needs to be executed / created. Jan 26, 2023 at 13:15
  • Point 5 isn't exactly valid here. You can generate the SomeOtherDatabase.sys.sp_executesql script automatically, such as using sys.databases to fill in all of the database names you care about. E.g. SELECT CONCAT([name], '.sys.sp_executesql') AS Script FROM sys.databases.
    – J.D.
    Jan 26, 2023 at 13:32
  • @J.D. Yes, and than you need to use the first sp_executesql call in the local database to call the just created script, which calls the second sp_executesql in the other database, which calls an EXEC () to finally create the script - as written in my solution. Or see you any way to do it in another way? Jan 27, 2023 at 20:12
  • 1
    @J.D. regarding the last EXEC () instead of sp_execute - it just a bit of lazyness, because I don't have to pass variables, the EXEC () works as well as a call of sp_execute. Why do you not try / post code (using my first two variables) that creates the v_test in the @dest_db without the tripple EXEC? To be honest, I see no way to do it. Jan 29, 2023 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.