"Paul Sieloff" <si*****@artronix-solutions.com> wrote in message
news:a6**************************@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''
')
