Why Can't I Create a View Inside of a Begin ... End Block

Why can't I create a view inside of a BEGIN ... END block

It's because CREATE VIEW must be the first statement in a batch as described in this MSDN reference.

Instead, you could do:
e.g.

.....
    BEGIN 
        EXECUTE('CREATE VIEW [dbo].[dummy] AS SELECT 1 AS Dummy')
    END

SQL: Why does CREATE VIEW have to be the first statement in a query batch?

About the only valid reason to need this ability is when checking for the existence of an object. This is something that sql server has struggled with because they lack the ability to Create Or Replace like many other databases do. That is how it works in sql server unfortunately.

In SQL Server 2016 they introduced Create Or Alter which deals with this.

As for the reason why this was not introduced earlier this would be a question to ask of the designers of the software which is not really answerable.

Can't create schema inside begin block

The error message is a bit of a red herring here.... Execute the following to see what the "real" error is:

SELECT * FROM sys.schemas
CREATE SCHEMA Test

Msg 111, Level 15, State 1, Line 2

'CREATE SCHEMA' must be the first statement in a query batch.

To get around this problem you can use the EXEC function:

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Test')
  BEGIN
    EXEC ('CREATE SCHEMA Test;');
  END;

CREATE VIEW must be the only statement in the batch

Just as the error says, the CREATE VIEW statement needs to be the only statement in the query batch.

You have two option in this scenario, depending on the functionality you want to achieve:

1. Place the CREATE VIEW query at the beginning

   CREATE VIEW showing
   as
   select tradename, unitprice, GenericFlag
   from Medicine;
   
   with ExpAndCheapMedicine(MostMoney, MinMoney) as
   (
       select max(unitprice), min(unitprice)
       from Medicine
   )
   ,
   findmostexpensive(nameOfExpensive) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
       where UnitPrice = MostMoney
   )
   ,
   findCheapest(nameOfCheapest) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
           where UnitPrice = MinMoney
       )
   

2. Use GO after the CTE and before the CREATE VIEW query

   -- Option #2

   with ExpAndCheapMedicine(MostMoney, MinMoney) as
   (
       select max(unitprice), min(unitprice)
       from Medicine
   )
   ,
   findmostexpensive(nameOfExpensive) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
       where UnitPrice = MostMoney
   )
   ,
   findCheapest(nameOfCheapest) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
       where UnitPrice = MinMoney
   )
   
   GO    
   
   CREATE VIEW showing
   as
   select tradename, unitprice, GenericFlag
   from Medicine;
   

Why can't I use create schema in a begin/end block in SQL Management Studio?

Schema creations must be the only statement in a batch. One way to get around it is like so:

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'acme')) 
BEGIN
    EXEC ('CREATE SCHEMA [acme] AUTHORIZATION [dbo]')
END

SQL: Can't Create SP Inside If Block

Stored procedures, triggers, functions, and several other object types need to be in their own batch. This can be achieved within an IF block by wrapping the CREATE statement in an EXEC(N'....'):

IF (OBJECT_ID(N'dbo.footTable') IS NULL)
BEGIN
   CREATE TABLE dbo.[fooTable]
   (
      ID INT NOT NULL IDENTITY(1, 1)
           CONSTRAINT [PK_fooTable] PRIMARY KEY,
      SomeNumber INT NOT NULL, 
      SomeOtherTable_ID INT NOT NULL
           CONSTRAINT [FK_fooTable_SomeOtherTable] FOREIGN KEY
           REFERENCES dbo.SomeOtherTable(SomeOtherTable_ID)
   );

   INSERT INTO dbo.fooTable (SomeNumber, SomeOtherTable_ID)
   VALUES (5, 1), (10, 1), (25, 1), (50, 1), (100, 1), (500, 1);

   EXEC(N'
        CREATE PROCEDURE dbo.myProcedureName
        AS
        SELECT SomeNumber FROM [fooTable];
   ');
END;

Notes:

• Be sure to include the capital-N prefix on string literals containing dynamic SQL (as a best practice -- yes, not having it often works, but having it always works).

• Don't use deprecated compatibility views such as sysobjects. Starting with the release of SQL Server 2005, the proper system catalog views are in the sys schema. In this case, it would be sys.objects.

• You should schema-qualify the objects: dbo.fooTable instead of just fooTable

• You should name your constraints: PK, FKs, etc.

Can't use IF for create or alter view depending on view's existense

Because ALTER/CREATE commands can't be within BEGIN/END blocks. You need to test for existence and the drop it before doing a create

IF Object_ID('TestView') IS NOT NULL
    DROP VIEW TestView

GO

CREATE VIEW TestView
   as
   . . .

GO

If you are woried about the permissions being lost you can script the GRANT statements as well and re-run those at the end.

You could wrap the create/alter into a string and do an EXEC - that might get ugly for large views

DECLARE @SQL as varchar(4000)

-- set to body of view
SET @SQL = 'SELECT X, Y, Z FROM TABLE' 

IF Object_ID('TestView') IS NULL
    SET @SQL = 'CREATE VIEW TestView AS ' + @SQL
ELSE    
    SET @SQL = 'ALTER VIEW TestView AS ' + @SQL

EXEC(@SQL)

Multiple statements inside one BEGIN ... END block

The column doesn't exist error is due to validation that occurs on existing objects. Since the table already exists, the parser / compiler will verify that the table contains all of the referenced columns.

In order to get around such timing issues with object verification, you can enclose the statement in an EXEC which will not be verified until run-time:

BEGIN
  ALTER TABLE [dbo].[UserProfiles]
    ADD [AllCheckboxesChecked] [bit]
    CONSTRAINT [DF_UserProfiles_AllCheckboxesChecked] DEFAULT 0
    NOT NULL;

  EXEC(N'UPDATE [dbo].[UserProfiles]
         SET [AllCheckboxesChecked]=1
         WHERE [CheckedBoxes] LIKE ''%#ALL#%''');
END;

Error while creating a view with procedure MySQL

A VIEW is not a procedure. A VIEW is only a single SELECT statement, which must have fixed columns at the time you define the VIEW. You can't make a VIEW that is also a procedure.

Sorry, if you need a pivot-table, you need to specify the values for each column in the query. You can't make a SELECT query or a VIEW that dynamically adds more columns as it finds potential future values.

And you can't define a VIEW that runs an arbitrary block of procedure code anyway. That would require a procedure.

You should just use the solutions that are already shown in questions like MySQL - Rows to Columns

There are no other shortcuts or workarounds.

By the way, all SQL databases have this restriction, not just MySQL.

---

Re your question:

I'm looking for a solution that doesn't require manual update of the query

A pivot-table query must have as many columns in the select-list as the number of columns you want it to return. There is no way to make an SQL query that expands the number of columns dynamically as a result of the data it reads at execute time.

The only way you can make a single query that returns all the data is to NOT do a pivot-table query, and instead return all the data in rows, not columns.

SELECT p.*, f.titolo, pf.id_persona IS NOT NULL AS ha_formazioni
FROM persone AS p 
CROSS JOIN formazioni AS f
LEFT OUTER JOIN formazioni_persone AS fp ON fp.id_formazioni AND fp.id_persona = p.id

This will return one row for each formazioni per persone. Then you must write code in your application to loop over all the rows of the reesult, and format the data in columns in the manner you want.

Use of Begin / End Blocks and the Go keyword in SQL Server?

GO is like the end of a script.

You could have multiple CREATE TABLE statements, separated by GO. It's a way of isolating one part of the script from another, but submitting it all in one block.

BEGIN and END are just like { and } in C/++/#, Java, etc.

They bound a logical block of code. I tend to use BEGIN and END at the start and end of a stored procedure, but it's not strictly necessary there. Where it IS necessary is for loops, and IF statements, etc, where you need more then one step...

IF EXISTS (SELECT * FROM my_table WHERE id = @id)
BEGIN
   INSERT INTO Log SELECT @id, 'deleted'
   DELETE my_table WHERE id = @id
END

---

Related Topics