How to resolve "Incorrect syntax near the keyword 'VIEW' error while creating view in dynamic SQL

Question

I have a use case in which I need to create views in multiple databases using dynamic SQL. I realized that I can't give database name in front of the view in dynamic SQL, so used the use database statement in the beginning of the dynamic SQL. I'm getting the Incorrect syntax near the keyword 'VIEW' error. I am wondering if it has anything to do with the create view statement in dynamic SQL? Any pointers will be helpful?

SET @Sql = N'

USE ' + @DbName + '

IF (EXISTS (SELECT * 
             FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = ''DBO'' 
             AND  TABLE_NAME = ''TAB_1''))
BEGIN

    IF OBJECT_ID(''[dbo].[VW_1]'') is not null
      DROP VIEW [dbo].[VW_1]
    
    CREATE VIEW [dbo].[VW_1]
    AS
    SELECT 
    ...
    ...

END


SET @Sql = 'EXEC (''' + REPLACE(@Sql, '''', '''''') + ''')' + CASE WHEN @ServerName = @ThisServer THEN '' ELSE ' AT ' + @ServerName END
EXEC ( @Sql )

Answer 1

From CREATE VIEW docs:

Remarks

---

A view can be created only in the current database. The CREATE VIEW must be the first statement in a query batch.

Since CREATE VIEW must be the first statement in a query batch you'll need to split your code into two pieces and run them separately somehow. That's why SSMS adds the GO command after USE db.

I don't know how to achieve that in dynamic SQL.

Maybe, build two dynamic SQL commands. The first command will have

USE db; 
-- some logic determining if the second command should run
EXEC 'CREATE VIEW AS ...'

The second command will have just the CREATE VIEW statement.