Executing Create View & Alter View From Sqlcmd

I'm trying to execute a sql file with the following contents using sql cmd. sqlcmd -S localhost\dbInstance -i Sample.sql -v filepath='C:\Sql\' Sample.sql contents: USE Sample_db

Solution 1:

As per the manual:

The CREATE VIEW must be the first statement in a query batch.

Although, to tell the truth, that statement is rather misleading, because in actual fact CREATE VIEW must be the only statement in the batch, as you can ascertain for yourself from this illustration of a very simple test:

The error message in the Messages pane says Incorrect syntax near keyword 'SELECT', but if you hover over the underscored CREATE VIEW statement, a hint message appears that reveals that you can't put anything neither before CREATE VIEW nor after its SELECT statement.

And it's precisely the same issue with ALTER VIEW.

So, you can have a CREATE VIEW and/or an ALTER VIEW statement(s) perform within a transaction (by delimiting them with GO keywords), but you will not be able to use BEGIN TRY ... BEGIN CATCH to catch exceptions raised by those statements.

Unless, as Aaron Bertrand correctly reminds me, you execute those statements as dynamic queries, using either EXEC(…) or EXEC sp_executesql …, something like this, perhaps:

…
 BEGIN TRY
   EXEC sp_executesql N'CREATE VIEW [dbo].[Test_View]ASSELECT * from Sample_table';   

   EXEC sp_executesql N'ALTER VIEW [dbo].[Sample_View]   ASSELECT * FROM table_9';        

   ALTER TABLE [Sample_Table_2] ADD Col_4 VARCHAR(20);

 ENDTRY 
 BEGIN CATCH     
…