Create procedure conditionally
-
I am writing a batch file that creates stored procedures, but I only want to create them if they don't yet exist. However, it seems that first statement has to be CREATE PROCEDURE statement. If I do
.....
if not exists (select * from dbo.sysobjects where id = object_id(N'xxx') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
CREATE PROCEDURE xxx
(
....
it gives me [Incorrect syntax near the keyword 'PROCEDURE'.]
Is there way around it? I don't want to have to drop them every time the script is run.
Many thanks,
Elena.
-
if not exists (select * from dbo.sysobjects where id = object_id(N'xxx') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
EXEC ('CREATE PROCEDURE xxx as select 1 as One')
GO
Alter PROCEDURE XXX
AS
...
_____________
Code for TallyGenerator -
Dynamic SQL is not much fun here. You'd have to convert your procedure to double-up embedded quotes, and the resulting code would be fairly unreadable (all in red for a start).
Since this is obviously an admin script, Id just run the CREATE PROC statements as a series of batches (i.e. separated by GO batch terminator). SQL will check and give you a message of the proc already exists.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
My "dynamic" does not have any quotes.
It always as simple as 'CREATE PROCEDURE xxx as select 1 as One'
Than the real scrpt goes, but it's included into ALTER statement. If procedure does not exist it's created by dummy script, so ALTER statement never gonna fail.
_____________
Code for TallyGenerator -
Sergiy - Messages are only errors ('failures') if you don't expect them. There's no requirement to alter anything here, just create procs if they don't already exist. For your requirement (which has the merit of ensuring you replace old versions), Oracle's CREATE OR REPLACE would be handy, wouldn't it!
I see what you mean though. Apologies - I didn't read your script properly and missed the ALTER section!
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
I would not think somebody could come to an idea to create the latest version of SP if it does not exist and leave whatever thing was written I don't know when by I don't know who if it exists.
Yes, CREATE OR REPLACE would be handy, but have I done worse than Oracle?
_____________
Code for TallyGenerator -
A person might have an environment which contains new versions of a only a subset of the procedures required. Perhaps only the procs from a particular functional area of a db app exist in the dev environment, and the rest of the procs used in that app need to be added for system testing to be undertaken.
We both made assumptions: I that the requirement was to be taken at face value, you that it wasn't. Maybe we should organise a requirement-capture refresher course...
Worse than Oracle? Only marginally so: in code readability and maintainability. But not in terms of job done, no.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply