I'm trying to create a dynamic Database creation script.
There are a lot of steps and we create this database often so the script looks something like this.
DECLARE @databaseName nvarchar(100) = 'DatabaseName' EXEC('/*A lot of database creation code built off of @databaseName*/')
This is all well and good except for one view we create in this database.
The problem as I understand it stems from three rules in SQL regarding the EXEC command
- USE contexts are only alive for the life of the EXEC
- 'CREATE VIEW' must be the first statement in a query batch
- GO isn't actually a SQL Command and as such isn't allowed in dynamic sql
So here are three things I've tried without success.
--1.Results in my view not being created in my database EXEC ('USE [' + @databaseName + ']') EXEC ('CREATE VIEW') --2.Results in a 'CREATE VIEW' must be the first statement in a query batch EXEC (' USE [' + @databaseName + '] CREATE VIEW ') --3.Results in Incorrect syntax near 'GO' EXEC (' USE [' + @databaseName + '] GO CREATE VIEW ')
Any suggestions? I think this should be a common use case but Google wasn't able to help me.