Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tsgood
Starting Member
2 Posts |
 Posted - 2002-08-22 : 16:05:53
|
| I often see hosts of sp's that begin with something like the following that drops and recreates the procedure:IF (SELECT count(*) FROM sysobjects WHERE _ name 'sp_ins_tblNames_Audit') > 0BEGIN DROP PROCEDURE sp_ins_tblNames_AuditENDGOCREATE PROCEDURE sp_ins_tblNames_Audit .... etc.GOI have been banging around various SQL sites trying to find some simple guidelines on when this drop/recreate should be used on sp's that are on a production system. I would assume doing this would have consequences, namely that the procedure has to be recompiled upon creation which has real costs.Any thoughts/hints/suggestions on when and when not to employ this strategy?Tim |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-08-22 : 17:06:04
|
| Tim, I always use this technique in my scripts (actually, lately I'm usingIf OBJECTPROPERTY(OBJECT_ID('SampleSproc'), 'IsProcedure') = 1 drop procedure SampleSprocGOBut it's the same idea. This is in the script that I write (and save in a version control system) to build my stored procedures when I make updates to them. This piece of code, being before a GO statement, before the CREATE PROCEDURE statement does not actually get included in the stored procedure itself. I just like to drop and create my procedures instead of ALTER them. Old habit long ingrained. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-08-23 : 09:48:13
|
| Stored procs are always compiled on first run (v7 onwards).You could use an alter procedure statement (like changing in enterprise manager) but this will not updte the creation date so you won't be able to check this to see what you have done.To change a production system create a script or each sp to change (with the conditional drop statement at the begining - for both cration and update), save this in sourcesafe.Then when you come to make updates you can save the new scripts and be able to check what has changed and have an automatic version history.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tsgood
Starting Member
2 Posts |
Posted - 2002-08-23 : 10:25:19
|
| Mark & nr,Thanks for your input. I like the version control suggestions.Also, Mark, is there some reason for moving to the OBJECTPROPERTY syntax when checking for the existence of the sp?Tim |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-08-23 : 10:31:39
|
um what if ms changes sysobjects ... you aren't *supposed* to access data in them ... the OBJECTPROPERTY as far as I know is a supported function that say if they change the type from 'P' to say 'SP' in the next version you'll have to change your code but they should keep the OBJECTPROPERTY(OBJECT_ID(N'ProcedureName'), N'IsProcedure') = 1 thingy working (in theory of course)  who knows they may get rid of it all in yukon and we'll be de-evolved back to static data rows and having to parse it like COBOL people do01234567890123456789012345678901234567890123456789Name Vendor Price Info ... ha ha I laugh everytime our COBOL programmers print these things ... they amuse me... damn legacy systems [rant]Edited by - onamuji on 08/23/2002 10:32:25 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-08-23 : 14:44:51
|
| For me, IF OJECTPROPERTY... just seemed simpler and more elegant.By the way, if you want to stay with the SELECT on sysobjects, then using IF EXISTS SELECT... is more efficient that IF ... COUNT(*)... > 1 syntax. Although, I don't know as sysobjects will get so large as to be a significant difference.Edited by - ajarnmark on 08/23/2002 14:45:11 |
|
|
|
|
|
|
|
|
When to drop procedures in sysobjects?