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]

Re: Create Procedure in an IF block?

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:[color=blue]
> 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?[/color]

[ITBurns]

Re: Create Procedure in an IF block?


cmay wrote:[color=blue]
> 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?[/color]

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]

Re: Create Procedure in an IF block?


Stu wrote:
[color=blue]
> 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:[color=green]
> > 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?[/color][/color]

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]

Re: Create Procedure in an IF block?

cmay (cmay@walshgrou p.com) writes:[color=blue]
> 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?[/color]

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[cmay]

Re: Create Procedure in an IF block?

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:[color=blue]
> cmay (cmay@walshgrou p.com) writes:[color=green]
> > 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?[/color]
>
> 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@sommarsk og.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[/color]

[Erland Sommarskog]

Re: Create Procedure in an IF block?

cmay (cmay@walshgrou p.com) writes:[color=blue]
> 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.[/color]

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx