December 2, 2003 at 4:55 pm
Has anybody experienced this in Query Analyzer?
A) Right Click on a User stored procedure in a User Database.
B) Choose "Script Object to New Window As"
C) Choose "Alter"
The CREATE PROCEDURE kewords are at the beginning of the Stored Procedure and NOT "ALTER PROCEDURE".
It turns out that if you create a Stored Procedure using the SQL Templates (or any other tool that saves comments at the beginning of the Stored Procedure - even manually) then this will happen to you.
The reason is that the right-click "ALTER" method simply uses the "CREATE" method and replaces ONLY the First instance of the word "Create" with "ALTER". So, if the word "Create" is in the Comments at the beginning of the Stored Procedure (before the original CREATE PROCEDURE key words), then only the "Create" in the comments gets replaced with "ALTER" and the CREATE PROCEDURE keywords are still active. However, if you try to execute the SQL statement you will get an Error stating that the object already exists.
Anyway...just something to keep in mind when using Comments at the beginning of a Stored Procedure.
Mike
December 3, 2003 at 9:34 am
Works fine for me (see ALTER). SQL 2K, SP3a, Personal.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 4, 2003 at 7:49 am
I gotta agree with Michael.
I tried it, also running 2K, SP3a, Personal, and got the results that he described. The key is to have a comment above the create statement that includes the word "CREATE" in it. I added the following line to a stored proc:
-- this is a comment with the word create included in it.
... so that the code looks like:
-- this is a comment with the word create included in it.
CREATE procedure af_timediff
When I used object browser to script to new window as ALTER, I got:
-- this is a comment with the word ALTER included in it.
CREATE procedure af_timediff
I can understand why it happens, and its easy enough to avoid/fix, but it IS interesting!
Steve Phelps
Edited by - hoo-t on 12/04/2003 07:51:37 AM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply