Create schema if not exists - problems

Hi,
I have a problem executing this statement on sqlserver2005:
>>
if not exists(select 1 from information_schema.schemata where
schema_name='testtest')
create schema TestTest AUTHORIZATION testuser;
go
<<<
I get a "Incorrect syntax near the keyword 'schema'", so what am i missing?!
TIA, TroyTroy,
CREATE SCHEMA must be the first statement in a batch, but the IF is the
first statement, so the CREATE SCHEMA fails. (Yes, I agree with what you
are thinking about that.)
Here is a workaround, which is a simple bit of dynamic SQL:
if not exists(select 1 from information_schema.schemata where
schema_name='testtest')
EXEC ('create schema TestTest AUTHORIZATION testuser');
go
RLF
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:14E88836-6CD9-4A9D-ACBF-FB7EAF5502F3@.microsoft.com...
> Hi,
> I have a problem executing this statement on sqlserver2005:
> if not exists(select 1 from information_schema.schemata where
> schema_name='testtest')
> create schema TestTest AUTHORIZATION testuser;
> go
> <<<
> I get a "Incorrect syntax near the keyword 'schema'", so what am i
> missing?!
> TIA, Troy|||Worked like a charm - Thanks :)
"Russell Fields" wrote:
> Troy,
> CREATE SCHEMA must be the first statement in a batch, but the IF is the
> first statement, so the CREATE SCHEMA fails. (Yes, I agree with what you
> are thinking about that.)
> Here is a workaround, which is a simple bit of dynamic SQL:
> if not exists(select 1 from information_schema.schemata where
> schema_name='testtest')
> EXEC ('create schema TestTest AUTHORIZATION testuser');
> go
> RLF
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:14E88836-6CD9-4A9D-ACBF-FB7EAF5502F3@.microsoft.com...
> > Hi,
> >
> > I have a problem executing this statement on sqlserver2005:
> >>
> > if not exists(select 1 from information_schema.schemata where
> > schema_name='testtest')
> > create schema TestTest AUTHORIZATION testuser;
> > go
> > <<<
> >
> > I get a "Incorrect syntax near the keyword 'schema'", so what am i
> > missing?!
> >
> > TIA, Troy
>
>sql