Create Procedure in an IF block?

[cmay]

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?

[Stu]

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?

[ITBurns]

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

[amish]

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

[Erland Sommarskog]

cmay (cm**@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[cmay]

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 (cm**@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

cmay (cm**@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx