Why Can't I Use "Create Schema" in a Begin/End Block in SQL Management Studio

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

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 Schema MS SQL won't work in IF-Statement

CREATE SCHEMA is one of these statements that must be alone in batch.

Try:

IF (SCHEMA_ID('AM') IS NULL) 
BEGIN
    EXEC ('CREATE SCHEMA [AM]')
END

For more info see : Why can't I use "create schema" in a begin/end block in SQL Management Studio?

TSQL - How to use GO inside of a BEGIN .. END block?

I had the same problem and finally managed to solve it using SET NOEXEC.

IF not whatever
BEGIN
    SET NOEXEC ON; 
END

ALTER TABLE dbo.EMPLOYEE ADD COLUMN EMP_IS_ADMIN BIT NOT NULL
GO
UPDATE dbo.EMPLOYEE SET EMP_IS_ADMIN = whatever

SET NOEXEC OFF; 

Many inserts inside a begin-end block

To counter the huge else block, you could use any one the the following 3 strategies.

STRATEGY 1

Use Bulk insert from SQL Server. Just dump all your data into a csv file and use the following statement in your else block. Store the csv file on your computer and give it's UNC path after FROM in statement below. This way you will only have a single line in your else block.

BULK INSERT dbo.MyTable
FROM '\\share\somepath\myTableInsertData.csv'
WITH (FORMAT = 'CSV'); 

STRATEGY 2

Write a stored procedure that inserts only x rows at a time. You could then call this stored procedure in a While loop and your else block would be very small. You would call this stored procedure repeatedly in a while loop from your original SQL and then the else block would end being just a few lines of t-sql code.

Note that you can control how many rows are inserted by the stored procedure at a time by using an appropriate value for the variable @numberOfRowsAtaTime. I have used 1000 so in a single call of stored procedure 1000 rows get inserted.

Of course, based on your business rules you can script the stored procedure's insert statements. If you have a pattern in your INSERTS then you could script that pattern into the stored procedure logic below.

Stored Procedure

CREATE PROCEDURE dbo.insertXRows
    @startIndex INT,
    @numberOfRows INT 
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @counter INT;
    SET @counter = @startIndex;

    WHILE @counter < (@startIndex + @numberOfRows -1)
    BEGIN 
    -- Insert statements for rows goes here
    --INSERT FOR @counter -- @counter would be different for each iteration
      SET @counter =  @counter + 1;
    END
END
GO

Your SQL

IF EXISTS (SELECT * FROM [MyTable])
BEGIN
    PRINT 'No need to insert data'
    --Stop executing script
END
ELSE
BEGIN

    declare @insertRowCounter int;
    set @insertRowCounter = 1;
    declare @numberOfRowsAtaTime int;
    set @numberOfRowsAtaTime = 1000;
    WHILE @insertRowCounter <= 150000
      EXEC dbo.insertXRows @insertRowCounter, @numberOfRowsAtaTime -- insert 1000 rows at a time
      SET @insertRowCounter = @insertRowCounter + @numberOfRowsAtaTime; 
    END
END

STRATEGY 3

Come up with 10 stored procedures so that each stored procedure has 15000 INSERTS. Then simply call these 10 stored procedures from your else block.

10 stored procedures

CREATE PROCEDURE dbo.insertProc1
  AS
BEGIN

    SET NOCOUNT ON;

    --INSERT1
    --INSERT2

     --INSERT15000

    END
END
GO

CREATE PROCEDURE dbo.insertProc2
  AS
BEGIN

    SET NOCOUNT ON;

    --INSERT15001
    --INSERT15002

     --INSERT3000

    END
END
GO

Your SQL

IF EXISTS (SELECT * FROM [MyTable])
BEGIN
    PRINT 'No need to insert data'
    --Stop executing script
END
ELSE
BEGIN
   EXEC insertProc1
   EXEC insertProc2
   EXEC insertProc3
   EXEC insertProc4
   EXEC insertProc5
   EXEC insertProc6
   EXEC insertProc7
   EXEC insertProc8
   EXEC insertProc9
   EXEC insertProc10
END

Create new database with schema from code

SSMS generated scripts contain go. In SSMS go ends a batch, but it is not so with ADO.NET's ExecuteCommand.

Split the script on go, and send each part separately using ExecuteCommand().

Running the script locally (or remotely) using SSMS should also work.

I'm not sure what you mean with "variable database name". In ADO.NET you can set the database in the connection string. In SSMS, you can use use <dbname> or the dropdown box in the query toolbar.

Views as Stored Procedures in Schema Change script

Since you are running schema update, you should comply minimal things like

• make it repeatable
• have it in your versioning system
• and test it in your testing environment before running in production.

In order to make it re-runable it's common practice to check if the object exists and drop it, and then to create it again.

Dynamic SQL will not add any functionality that the IF clause has.

For example:

IF EXISTS(SELECT * FROM sys.views WHERE name = 'MyView')
BEGIN
    DROP VIEW [MyView]
END
GO
CREATE VIEW MyView AS 
    SELECT [Columns list ]
    FROM [MyTable]
GO

The only case that I know which justifies dynamic SQL is if you want to preserve grant / deny, in such case you can use following syntax.

IF NOT EXISTS(SELECT * FROM sys.views WHERE name = 'MyView')
BEGIN
    EXEC sp_executesql N'CREATE VIEW MyView AS SELECT 1 AS Dummy'
END
GO
ALTER VIEW MyView AS 
    SELECT [Columns list ]
    FROM [MyTable]

go
GRANT .... 

I wouldn't create a Dynamic SQL to fill the few times that I have to repeat the names in any of the scripts below. IMHO the deployment script should be as plain as possible.

---

Related Topics