April 19, 2012 at 7:57 am
For a existing proc, which way is better for a script execution in different environment:
1.just alter proc statement
2.Drop and create proc statement...
Although I preferno. 2, since it never fails irrespective of a proc exists or nor. But would like to know if there can be any downside to it....
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 19, 2012 at 8:02 am
the downside to 2 IMHO, is that the drop will succeed and the the create will fail....
in option 1, if the alter fails, no harm no foul.
also, an alter does not require you to run grant's on the existing stored procedure...
April 19, 2012 at 8:31 am
Geoff A (4/19/2012)
the downside to 2 IMHO, is that the drop will succeed and the the create will fail....in option 1, if the alter fails, no harm no foul.
also, an alter does not require you to run grant's on the existing stored procedure...
+1 Why drop the proc and take the risk that the fail will create(DOH!) create will fail? What's the point of writing extra script to even do it? Just alter it...
Jared
CE - Microsoft
April 19, 2012 at 8:37 am
Also, since SQL Server 2005, the date/time of when database objects are modified is now captured in the new system views.
April 19, 2012 at 8:41 am
Ofcourse there is some extra code in script,but it makes sure that when i run it in different environments, it never fails.sometimes the proc may just not be there in some environmet and hence failing the script.
But I got your point that if creation fails, then proc may not remain available untill created again. Failing of alter script on other hand won't harm anything.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 19, 2012 at 8:42 am
Good point Lynn....
Lynn Pettis (4/19/2012)
Also, since SQL Server 2005, the date/time of when database objects are modified is now captured in the new system views.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 19, 2012 at 8:45 am
S_Kumar_S (4/19/2012)
Ofcourse there is some extra code in script,but it makes sure that when i run it in different environments, it never fails.sometimes the proc may just not be there in some environmet and hence failing the script.But I got your point that if creation fails, then proc may not remain available untill created again. Failing of alter script on other hand won't harm anything.
Plus, if the SP is not there... Change the word ALTER to CREATE. Pretty simple, right? 😉
Jared
CE - Microsoft
April 19, 2012 at 8:49 am
Yes Of course:-). So I need to change my habit of drop create...
SQLKnowItAll (4/19/2012)
S_Kumar_S (4/19/2012)
Ofcourse there is some extra code in script,but it makes sure that when i run it in different environments, it never fails.sometimes the proc may just not be there in some environmet and hence failing the script.But I got your point that if creation fails, then proc may not remain available untill created again. Failing of alter script on other hand won't harm anything.
Plus, if the SP is not there... Change the word ALTER to CREATE. Pretty simple, right? 😉
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 19, 2012 at 8:52 am
I do have to say I liked Oracle's CREATE OR REPLACE. If the database object (view, stored procedure, function) already existed, it replaced it and if it didn't exist it created it.
April 19, 2012 at 8:53 am
Lynn Pettis (4/19/2012)
I do have to say I liked Oracle's CREATE OR REPLACE. If the database object (view, stored procedure, function) already existed, it replaced it and if it didn't exist it created it.
BLASPHEMY! 😀
Jared
CE - Microsoft
April 19, 2012 at 9:00 am
SQLKnowItAll (4/19/2012)
Lynn Pettis (4/19/2012)
I do have to say I liked Oracle's CREATE OR REPLACE. If the database object (view, stored procedure, function) already existed, it replaced it and if it didn't exist it created it.BLASPHEMY! 😀
Having worked with both, I can see some good and bad in both.
Another thing I liked about Oracle, subquery refactoring clauses (aka SQL Server, CTEs). Oracle could treat these as in-line queries or temporary tables depending on how they were used. Imagine if SQL Server created a temporary table of a CTE that was used multiple times in a query rather than running multiple times in-line.
Bottom line, however, I cut my teeth on SQL Server and in the end I believe that SQL Server does things better. It just needs to catch up in some areas, and it seems to be going in that direction.
April 19, 2012 at 12:42 pm
Lynn Pettis (4/19/2012)
I do have to say I liked Oracle's CREATE OR REPLACE. If the database object (view, stored procedure, function) already existed, it replaced it and if it didn't exist it created it.
I have been hoping for this feature to be brought into SQL Server for a long time...
Please vote!
http://connect.microsoft.com/SQLServer/feedback/details/127219/create-or-replace
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2012 at 2:44 pm
Another point is if you drop the sp, the associated permission is dropped as well. You have re-grant. 😉
April 20, 2012 at 5:15 pm
Don't bother in droping and re-creating, Ive alter many stored procedures without any problem....
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply