if -- else with ALTER PROC and CREATE PROC

Question

if -- else with ALTER PROC and CREATE PROC

linto

SSC Eights!

Points: 851
More actions
January 16, 2004 at 2:07 pm

#108659

What can be done to create/alter a stored procedure.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RPT1_US]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
ALTER PROCEDURE dbo.RPT1_US AS .........
GO
END
ELSE
BEGIN
CREATE PROCEDURE dbo.RPT1_US AS .........
GO
END

its coming with an error.

any fast answers ?
Linto

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 1

alter or create need to be the first thing in the batch. I typically always drop and recreate, but I keep the security with the code.

gljjr SSChampion Points: 10080 More actions · Answer 2

I'm like Steve, most of my scripts do a drop/create with permissions tacked on at the end.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

I agree. Drop and create.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

linto SSC Eights! Points: 851 More actions · Answer 4

We have to go through security department to get EXEC permissions on the stored procedure. In most cases, our Software Config Management group has permissions to run DDL statements. And in most upgrade of application require only ALTER and no CREATE..

Linto

linto SSC Eights! Points: 851 More actions · Answer 5

Can I use

if exists(.....)

GOTO label1

else

GOTO label2

GO

label1:

ALTER PROC .....

GOTO endlabel

GO

label2:

CREATE PROC:

GO

endlabel:

GO

Whats your thoughts ??

linto SSC Eights! Points: 851 More actions · Answer 6

linto

SSC Eights!

Points: 851

January 19, 2004 at 7:19 am

#490658

oops!!!!! no way......

guarddata SSCertifiable Points: 6583 More actions · Answer 7

We generally do DROP and CREATE... However, we have found it useful on some occasions to have "empty" procedures to avoid the warnings of "procedure not found". When this happens, we do

IF NOT EXISTS ( .... )

EXEC sp_executesql N'CREATE PROCEDURE <procname> AS RETURN'

GO

ALTER PROCEDURE <>....

This allows the ALTER to be the first executed statement, but does not perform a DROP if you didn't want it.

Just $0.02

Guarddata-

linto SSC Eights! Points: 851 More actions · Answer 8

linto

SSC Eights!

Points: 851

January 19, 2004 at 10:54 am

#490704

that worked......

Thanks... Linto

gljjr SSChampion Points: 10080 More actions · Answer 9

guardata,

That is a very good work around! I may just have to do that. The previous DBA replicated all my Sprocs with merge replication so I can't actually drop and recreate them. I just alter them. Even though my scripts are currently drop, create I think I will change them to be create, alter. Seems a more elegant solution than having to alter the script everytime I need to update a replicated sproc. Had the DBA just used Snapshot replication instead for the sprocs I wouldn't have such a pain keeping everyone in sync!

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 10

We use merge replication too, but we do not replicate stored procedures. It's just a matter of unchecking the appropriate boxes. You don't have to change replciation type.

Also, I hope he's not using merge replication when it's not needed. It's great when you need it, but it adds a lot of overhead and maintenace to the replicaion process that transactional and snapshot just don't have.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

gljjr SSChampion Points: 10080 More actions · Answer 11

Ahh, but if I remove the articles from the subscription then I have to re-initialize all the subscribers. Due to the way our database is set up that is something that is not a trivial thing at all!

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 12

Yes, that's true. Changing it is a major thing. Setting it up that way to start with is very simple.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]