CREATE SCHEMA fails Inside an If Block

Hello All,

The below "CREATE SCHEMA" sql statement fails if it is inside an IF block. It runs fine if i run it without the IF block...

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

BEGIN

CREATE SCHEMA Customer AUTHORIZATION [sys]

END

-

Did anyone encountered this issue before....

Thanks..

Make this as dynamic SQL.|||

Thanks, Bushan.

As the DDL scripts can grow bigger, I feel that it is hard to maintain dynamic sql. But, I was just trying to figure out why this is not possible in this "create schema" scenario alone. It even works for "drop schema".

|||

Like the error message you get back says, CREATE SCHEMA must be the first command in the batch. So to do a CREATE SCHEMA in a script like this, that one statement has to be done dynamically.

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Customer')
BEGIN
exec ('CREATE SCHEMA Customer AUTHORIZATION [sys]')
END
Annoying? Yes? But it is not that much more work than doing it in the way that you (and I) originally expected :)

|||This is such an OBVIOUS shortcoming of TransactSQL. Why hasn't Microsoft corrected this flaw. As far as I know (with the exception of entering the exec ('sneak the CREATE in as a text string') there is no way to conditionally create a SCHEMA or a FUNCTION for that matter.

If it's disallowed for security reasons then why can we sneak it in with an EXEC?

The problem this causes for me (over and over again) is I write a sample function for the user and include it in my upgrade script. If they've already run the script for an earlier version, they already have my example and may have customized it to their specific application. If they have, I don't really want to replace it with my example again. So I'm stuck sneaking it in through the string route. This has been a flaw in T-SQL for a long time....

Sorry for the rant... Sure wish the T-SQL gods were listening |||
Thanks for the clarification..