If Exists Schema Fails

Could someone explain why

IF SCHEMA_ID('TestSchema') is nul CREATE SCHEMA TestSchema

Fails with Incorrect syntax near the keyword 'SCHEMA'.

Where as running the following statements

CREATE SCHEMA TestSchema

and

IF SCHEMA_ID('TestSchema') is null SELECT 1

succeeds without a problem.

Thanks

_GJK

Ok. I found the solution from

http://groups.google.com/group/microsoft.public.sqlserver.newusers/browse_thread/thread/69a4e9c45d045120/77c693f5043fbddc?lnk=st&q=schema_id&rnum=1#77c693f5043fbddc

|||

Actually the real root of your problem is that CREATE SCHEMA must be the first statement in a batch. Using dynamic SQL (as suggested in the link above) is a workaround as the dynamic statement itself is considered a new batch.

-Raul Garcia

SDE/T

SQL Server Engine