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..

Create Procedure in an IF block?

I am writing some code generation stuff and I am trying to get a script
like this to work:
IF (something)
BEGIN
CREATE PROCEDURE Whatever
AS
SELECT 1 as one
END
But it complains about this, so I am guessing that I can't put the
create prodcedure in an IF block.
Does anyone know of a work around for this?It's generally not a good idea to dynamically create stored procedures;
why are you trying to do that? Perhpas there's a better way to solve
the problem you're trying to do.
Stu
cmay wrote:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?|||cmay wrote:
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?
If your procedure is not too complex to declare in a string, you could
create a variable that includes the CREATE PROCEDURE command and then
execute it with sp_executesql:
IF (1=1)
BEGIN
DECLARE @.sql nvarchar(1000)
SET @.sql = 'CREATE PROCEDURE Whatever
AS
SELECT 1 as one'
EXEC sp_executesql @.sql
END|||Stu wrote:
> It's generally not a good idea to dynamically create stored procedures;
> why are you trying to do that? Perhpas there's a better way to solve
> the problem you're trying to do.
> Stu
> cmay wrote:
You can use dynamic sql
IF (something)
BEGIN
exec(' CREATE PROCEDURE Whatever
AS
SELECT 1 as one')
END
But procedures are generally permenent object and why are you
interested to create them on the fly?
Regards
Amish shah|||cmay (cmay@.walshgroup.com) writes:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?
What is the real purpose of this? Using T-SQL to generate code sounds
utterly painful to me. As pointed out in another post, you would have to
use dynamic SQL, but only do this if you like to hurt yourself.
If the purpose is simply to write an installation script, I recommend that
you write the installation script in a client language: Perl, VB, VBscript
or whatever.
For more information on dynamic SQL, see
http://www.sommarskog.se/dynamic_sql.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am using a Code Generation program that creates a script for the
necessary stored procedures.
I guess I could put them in a big string, but I would have to make sure
I escaped all my single quotes.
Erland Sommarskog wrote:
> cmay (cmay@.walshgroup.com) writes:
> What is the real purpose of this? Using T-SQL to generate code sounds
> utterly painful to me. As pointed out in another post, you would have to
> use dynamic SQL, but only do this if you like to hurt yourself.
> If the purpose is simply to write an installation script, I recommend that
> you write the installation script in a client language: Perl, VB, VBscript
> or whatever.
> For more information on dynamic SQL, see
> http://www.sommarskog.se/dynamic_sql.html.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||cmay (cmay@.walshgroup.com) writes:
> I am using a Code Generation program that creates a script for the
> necessary stored procedures.
> I guess I could put them in a big string, but I would have to make sure
> I escaped all my single quotes.
Ah, if you are using some program to generate the input script, putting
the CREATE PROCEDURE in dynamic SQL is a fair game. Of course you need
to double all the single quotes, and if the procedure itself employs
dynamic SQL, the result can be about unreadable. But as long as the result
is not meant to be read - who cares?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Create Procedure in an IF block?

I am writing some code generation stuff and I am trying to get a script
like this to work:

IF (something)
BEGIN
CREATE PROCEDURE Whatever
AS
SELECT 1 as one
END

But it complains about this, so I am guessing that I can't put the
create prodcedure in an IF block.

Does anyone know of a work around for this?It's generally not a good idea to dynamically create stored procedures;
why are you trying to do that? Perhpas there's a better way to solve
the problem you're trying to do.

Stu

cmay wrote:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?|||cmay wrote:
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?

If your procedure is not too complex to declare in a string, you could
create a variable that includes the CREATE PROCEDURE command and then
execute it with sp_executesql:

IF (1=1)
BEGIN
DECLARE @.sql nvarchar(1000)
SET @.sql = 'CREATE PROCEDURE Whatever
AS
SELECT 1 as one'
EXEC sp_executesql @.sql
END|||Stu wrote:

> It's generally not a good idea to dynamically create stored procedures;
> why are you trying to do that? Perhpas there's a better way to solve
> the problem you're trying to do.
> Stu
> cmay wrote:
> > I am writing some code generation stuff and I am trying to get a script
> > like this to work:
> > IF (something)
> > BEGIN
> > CREATE PROCEDURE Whatever
> > AS
> > SELECT 1 as one
> > END
> > But it complains about this, so I am guessing that I can't put the
> > create prodcedure in an IF block.
> > Does anyone know of a work around for this?

You can use dynamic sql
IF (something)
BEGIN
exec(' CREATE PROCEDURE Whatever
AS
SELECT 1 as one')
END

But procedures are generally permenent object and why are you
interested to create them on the fly?

Regards
Amish shah|||cmay (cmay@.walshgroup.com) writes:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?

What is the real purpose of this? Using T-SQL to generate code sounds
utterly painful to me. As pointed out in another post, you would have to
use dynamic SQL, but only do this if you like to hurt yourself.

If the purpose is simply to write an installation script, I recommend that
you write the installation script in a client language: Perl, VB, VBscript
or whatever.

For more information on dynamic SQL, see
http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am using a Code Generation program that creates a script for the
necessary stored procedures.

I guess I could put them in a big string, but I would have to make sure
I escaped all my single quotes.

Erland Sommarskog wrote:
> cmay (cmay@.walshgroup.com) writes:
> > I am writing some code generation stuff and I am trying to get a script
> > like this to work:
> > IF (something)
> > BEGIN
> > CREATE PROCEDURE Whatever
> > AS
> > SELECT 1 as one
> > END
> > But it complains about this, so I am guessing that I can't put the
> > create prodcedure in an IF block.
> > Does anyone know of a work around for this?
> What is the real purpose of this? Using T-SQL to generate code sounds
> utterly painful to me. As pointed out in another post, you would have to
> use dynamic SQL, but only do this if you like to hurt yourself.
> If the purpose is simply to write an installation script, I recommend that
> you write the installation script in a client language: Perl, VB, VBscript
> or whatever.
> For more information on dynamic SQL, see
> http://www.sommarskog.se/dynamic_sql.html.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||cmay (cmay@.walshgroup.com) writes:
> I am using a Code Generation program that creates a script for the
> necessary stored procedures.
> I guess I could put them in a big string, but I would have to make sure
> I escaped all my single quotes.

Ah, if you are using some program to generate the input script, putting
the CREATE PROCEDURE in dynamic SQL is a fair game. Of course you need
to double all the single quotes, and if the procedure itself employs
dynamic SQL, the result can be about unreadable. But as long as the result
is not meant to be read - who cares?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Create index question

Will creating an index while a database is in use block user access?
Thanks.If you're on SQL Server 2000, or on SQL Server 2005 with any SKU apart from
Enterprise Edition, then creating an index is an offline operation. This
means that creating a clustered index takes an exclusive table lock and
read/write access to the table is blocked. It also means that creating a
non-clustered index takes a shared table lock to block only write access to
the table.
If you're on SQL Server 2005 Enterprise Edition, you can make use of the
various online index operations, which do not hold long-term blocking
lockss. See Books Online for CREATE INDEX for more details.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Kelley" <tkelley@.company.com> wrote in message
news:uwIAu0GzGHA.3704@.TK2MSFTNGP02.phx.gbl...
> Will creating an index while a database is in use block user access?
> Thanks.
>|||It depends on what SQL Server version you are using. SQL Server 2000 and
older will block user access. In SQL Server 2005 it could be an online
operation depending on the kind of index operation you are going to perform.
See Create Index in SQL Server 2005 Books online for more details on what
index operations are online and what are offline.
Bob
"Tim Kelley" wrote:
> Will creating an index while a database is in use block user access?
> Thanks.
>
>|||Hi Tim
Even if you're using SQL 2000, you can always kill off a create index
command without much penalty so why not just try it out & kill the command
if you're blocking users too long? You might be surprised how fast some
indexes on otherwise seemingly large tables can be created..
I often use the script at the URL below with SQL 2000 to monitor blocking
from another session during index creation & just kill off the index
creation if necessary. This often gets the job done without requiring a
system outage.
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2005/10/02/50.aspx
SQL 2005 is a different story of course, due the online indexing
capability..
Regards,
Greg Linwood
SQL Server MVP
"Tim Kelley" <tkelley@.company.com> wrote in message
news:uwIAu0GzGHA.3704@.TK2MSFTNGP02.phx.gbl...
> Will creating an index while a database is in use block user access?
> Thanks.
>

Create index question

Will creating an index while a database is in use block user access?
Thanks.If you're on SQL Server 2000, or on SQL Server 2005 with any SKU apart from
Enterprise Edition, then creating an index is an offline operation. This
means that creating a clustered index takes an exclusive table lock and
read/write access to the table is blocked. It also means that creating a
non-clustered index takes a shared table lock to block only write access to
the table.
If you're on SQL Server 2005 Enterprise Edition, you can make use of the
various online index operations, which do not hold long-term blocking
lockss. See Books Online for CREATE INDEX for more details.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Kelley" <tkelley@.company.com> wrote in message
news:uwIAu0GzGHA.3704@.TK2MSFTNGP02.phx.gbl...
> Will creating an index while a database is in use block user access?
> Thanks.
>|||It depends on what SQL Server version you are using. SQL Server 2000 and
older will block user access. In SQL Server 2005 it could be an online
operation depending on the kind of index operation you are going to perform.
See Create Index in SQL Server 2005 Books online for more details on what
index operations are online and what are offline.
Bob
"Tim Kelley" wrote:

> Will creating an index while a database is in use block user access?
> Thanks.
>
>|||Hi Tim
Even if you're using SQL 2000, you can always kill off a create index
command without much penalty so why not just try it out & kill the command
if you're blocking users too long? You might be surprised how fast some
indexes on otherwise seemingly large tables can be created..
I often use the script at the URL below with SQL 2000 to monitor blocking
from another session during index creation & just kill off the index
creation if necessary. This often gets the job done without requiring a
system outage.
http://blogs.sqlserver.org.au/blogs...5/10/02/50.aspx
SQL 2005 is a different story of course, due the online indexing
capability..
Regards,
Greg Linwood
SQL Server MVP
"Tim Kelley" <tkelley@.company.com> wrote in message
news:uwIAu0GzGHA.3704@.TK2MSFTNGP02.phx.gbl...
> Will creating an index while a database is in use block user access?
> Thanks.
>