June 16, 2005 at 10:04 am
Hi
Someone I worked with told me that dropping and recreating a stored procedure could result in a dependent trigger or procedure being dropped as well. Is this true? He suggested I use Alter Procedure instead which I've always understood is a good way to preserve any permissions that may have been granted to the procedure in question.
I looked up ALTER PROCEDURE in BOL and it says "
Alters a previously created procedure, created by executing the CREATE PROCEDURE statement, without changing permissions and without affecting any dependent stored procedures or triggers."
I'm not sure what this is getting at although it suggests that dropping a stored procedure would somehow effect dependent procedures and triggers.
To test this, I just created a stored procedure named x that selects some records from a table. I then created a stored procedure named x2 that simply executes x. I then dropped procedure x without any warnings from SQL Server that x couldn't be dropped because it's depended upon by x2. Is there some sort of server configuration that should be set to enforce dependencies and that would have prevented the dropping of procedure x in this example?
More generally is there any scenario whereby dropping a procedure would result in the dropping of a procedure/trigger or else would result in some sort of negative impact on a proc/trigger (other than a dependent proc/trigger no longer working).
I'll appreciate any feedback you can offer.
Thanks!
June 16, 2005 at 10:31 am
You have nothing to fear on that matter.
The only slight ill effect is that when you drop/recreate a proc, the dependencies to other objects are lost because they are deleted and not reinserted untill you alter all the other objects dependant on it.
Can he show you a sample code that demonstrate this behavior?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply