I am trying to conditionally create a stored procedure in a Query
Analyzer script. When I have the CREATE PROCEDURE statement by itself
in the script, it compiles and runs ok. Once I put a condition on the
script, it does not run.

I have tried wrapping the script inside a BEGIN...END but it does not
help.

ex:
IF (SELECT [Version] FROM [tblDefaults]) < '5.11a'
CREATE PROCEDURE [dbo].[SelectByAccount]

@ARAcct int
AS

SELECT * FROM tblTable WHERE [AccountNo] = @Acct

This does not work.

I have also tried wrapping the entire CREATE PROCEDURE statement in an
EXEC() but that does not work when I have literals in the select
statement.

HELP!!
Thanks
Paul

"Paul Sieloff" <sieloff@artronix-solutions.com> wrote in message
news:a6a6554b.0402201026.5eca4873@posting.google.c om...
> I am trying to conditionally create a stored procedure in a Query
> Analyzer script. When I have the CREATE PROCEDURE statement by itself
> in the script, it compiles and runs ok. Once I put a condition on the
> script, it does not run.
>
> I have tried wrapping the script inside a BEGIN...END but it does not
> help.
>
> ex:
> IF (SELECT [Version] FROM [tblDefaults]) < '5.11a'
> CREATE PROCEDURE [dbo].[SelectByAccount]
>
> @ARAcct int
> AS
>
> SELECT * FROM tblTable WHERE [AccountNo] = @Acct
>
> This does not work.
>
> I have also tried wrapping the entire CREATE PROCEDURE statement in an
> EXEC() but that does not work when I have literals in the select
> statement.
>
> HELP!!
> Thanks
> Paul

CREATE PROCEDURE must be the only statement in the batch, so you can't
conditionally create one with IF. You can use EXEC() to do this, though, and
I guess that what you mean by 'does not work' is that you haven't doubled up
the quotes around values in your code (see example below).

You don't give any information about why you're trying to do this, but if
it's part of a deployment or migration script, you might find it easier to
use a client tool which connects to the database, then conditionally creates
the objects.

Finally, you may want to review your version logic - using the syntax above,
version '5.9a' is greater than '5.11a' and version '10.11a' is less than it,
based on string comparison.

Simon

if (select Version from dbo.tblDefaults) < '5.11a'
exec ('
create procedure dbo.MyProc
as
select col1 from dbo.MyTable where col2 = ''A''
')

Paul Sieloff (sieloff@artronix-solutions.com) writes:
> I am trying to conditionally create a stored procedure in a Query
> Analyzer script. When I have the CREATE PROCEDURE statement by itself
> in the script, it compiles and runs ok. Once I put a condition on the
> script, it does not run.
>
> I have tried wrapping the script inside a BEGIN...END but it does not
> help.
>
> ex:
> IF (SELECT [Version] FROM [tblDefaults]) < '5.11a'
> CREATE PROCEDURE [dbo].[SelectByAccount]
>
> @ARAcct int
> AS
>
> SELECT * FROM tblTable WHERE [AccountNo] = @Acct
>
> This does not work.
>
> I have also tried wrapping the entire CREATE PROCEDURE statement in an
> EXEC() but that does not work when I have literals in the select
> statement.

The CREATE PROCEDURE must be the first in the batch, why this does
not work.

In short, trying to run all in SQL is a very tediuos affair, as you
need to embed all code in EXEC() and that.

It's better to write a program in your favourite client language:
VBscript, Perl or even a BAT file, and have the client program to
do the controlling logic.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp