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