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

EnrightMcC

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?

EnrightMcC

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.

Jessica R

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;

EnrightMcC

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