none
IF NOT EXISTS and CREATE SCHEMA RRS feed

  • Question

  • I have noticed the problem with CREATE SCHEMA inside IF

    for example:

    IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name='app') create SCHEMA [app]

    generates syntax error

    is it bug?

    PS.

    This same effect with WHILE

    WHILE (SELECT 1 FROM sys.schemas WHERE name='app') !=1 CREATE SCHEMA app

    it looks that CREATE SCHEMA is incorrectly parsed inside complex syntax.

    Thursday, October 18, 2018 1:55 PM

All replies

  • Hi,

    CREATE SCHEMA must be in its own batch, that's why you have the error.

    But you have an alternative way to do the same thing with dynamic SQL :

    IF NOT EXISTS ( SELECT  *
                    FROM    sys.schemas
                    WHERE   name = N'app' )
        EXEC('CREATE SCHEMA [app]');
    GO


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Thursday, October 18, 2018 2:10 PM
  • Thanks a lot. The EXEC statement solved my problem.

    However other DDL's - CREATE DATABASE, ROLE work well in my proposed IF syntax.

    Are you sure that it is the CREATE SCHEMA limitation - for me it looks that parser do something wrong?

    Thursday, October 18, 2018 2:17 PM
  • I'm 100% sure, the create schema must be the only statement in a batch.

    https://garywoodfine.com/check-if-schema-exists-in-sql-server-database/


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]


    Thursday, October 18, 2018 2:23 PM
  • Try this one.

    IF NOT EXISTS ( SELECT  * FROM    sys.schemas  WHERE   name = N'app' ) 

        EXEC('CREATE SCHEMA [app] AUTHORIZATION [DBO]');
    GO

    Thursday, October 18, 2018 7:53 PM
  • However other DDL's - CREATE DATABASE, ROLE work well in my proposed IF syntax.

    Are you sure that it is the CREATE SCHEMA limitation - for me it looks that parser do something wrong?

    There are some CREATE commands that must be alone in a batch, for instance CREATE PROCEDURE, CREATE TRIGGER, CREATE VIEW etc.

    Yes, it's a little odd that CREATE SCHEMA which does not seem to have any definition that folows must be alone in a batch, but there is an older form (very rarely used) of CREATE SCHEMA that does accept more text.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, October 18, 2018 10:20 PM
  • Hi Zbigniew Szmigiero,

    Per your description, it is not a bug. You can find 'This statement must be executed as a separate batch. ' in the official documentation. For more details, you can refer to this article: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql?view=sql-server-2017

     

    By the way, you can try following script.

    IF EXISTS (SELECT name FROM sys.schemas WHERE name = N'app')
       BEGIN
          DROP SCHEMA [app]
    END
    GO
    CREATE SCHEMA [app] 
    GO


    Hope it can help you.

     

    Best Regards

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 19, 2018 3:06 AM
  • Are you sure that it is the CREATE SCHEMA limitation


    It's documented under CREATE SCHEMA (Transact-SQL) => Remarks: "This statement must be executed as a separate Batch"

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, October 19, 2018 5:42 AM