What are the challenges you face when working across database platforms? Take the survey
Options

Format SQL, Apply Layout isn't working on CREATE SCHEMA following an IF NOT EXISTS

EnrightMcCEnrightMcC Posts: 14 Bronze 2
Why can't this code be formatted (Ctrl K+Y)?
***
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'staging')
CREATE SCHEMA staging;
****
Error in 'apply layout (format sql)'
Script parsing errors (2):
Ln:2 Col: 2 - incorrect syntax near CREATE
Ln:2 Col: 9 - incorrect syntax near SCHEMA

SQL Prompt Version 9.4.6807

Replacing the CREATE SCHEMA works, Using only the CREATE SCHEMA works; but when I have them together, it doesn't work.  I've tried BEGIN/END blocks; and everything else I could think of.  What's wrong?

Answers

  • Options
    EnrightMcCEnrightMcC Posts: 14 Bronze 2
    I see sql compare does something entirely different: 
    IF SCHEMA_ID(N'staging') IS NULL
    EXEC sp_executesql N'CREATE SCHEMA [staging]
    AUTHORIZATION [staging]'
    GO
    But I'm still curious what's wrong with that SQL?  I even tried putting it in a transaction but it throws a syntax error.
  • Options
    Jessica RJessica R Posts: 1,319 Rose Gold 4
    Hi @EnrightMcC!

    I suspect it's because the CREATE SCHEMA statement needs to be the only statement in the batch. SSMS shows an error on that code for me as well, even with the BEGIN and END block. 

    I'm curious, does that code execute for you?

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

    CREATE SCHEMA staging;

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • Options
    EnrightMcCEnrightMcC Posts: 14 Bronze 2
    Nope. Like you said, the create schema needs to be a standalone statement.  Today I learned something. :-)  After exercising my google-fu, it's become very clear the CREATE statement has to be a stand-alone statement, or the very first statement in a batch.  
    Thanks,
    EnrightMcC
Sign In or Register to comment.