Modifying Stored Procedures

If we drop a stored procedure and then use CREATE PROCEDURE to create it again, we would have to reassign the execute permissions that existed on the stored procedure before. If the permissions are to be retained, then we can use the ALTER PROCEDURE statement. The downside is that the creation date is not altered, so if you do have tools to check what procedures have changed since a specific date, then ALTER PROCEDURE will not pick this up.

The ALTER PROCEDURE statement will also reset the QUOTED_IDENTIFIER and ANSI_NULL parameters, just as a DROP and CREATE will, so take care to maintain the same settings, as there were before, when working on a stored procedure.

Note

It is best to have these as company standards so that problems don't exist upon any alteration.

It is also possible to rename a stored procedure in T-SQL by using sp_rename. If you rename a stored procedure, run sp_depends, which will give a list of all the dependencies on the procedure. Any other stored procedures listed will also have to be modified, otherwise they will not be able to find this procedure.

Note

The system stored procedure sp_depends will list all the objects dependent on the object name that is passed to it.

Recompiling

When a stored procedure is created and placed in SQL Server, it will sit in the database until it is executed for the first time. At that point, SQL Server compiles the procedure optimising it based on current statistics on the tables used. If it is a subsequent execution, then there are two possible scenarios:

• The stored procedure is no longer in the procedure cache
  This means that SQL Server has aged the plan out of memory. At this point the stored procedure is recompiled back into the cache and then executed.

• The stored procedure is still in the procedure cache
  Then, the execution plan is taken from the cache and executed.

If an index or even columns are added to a table, and if they are likely to alter the query plan that has been built up for the set of tables used, then we should recompile all the dependent stored procedures, otherwise our stored procedure may not utilize the best method for accessing the data.

It is possible to set up a stored procedure to recompile every time it is executed, for example, when the stored procedure is run weekly to gather information about what has happened to the data over the week. Here, you may build a stored procedure that takes a look at share price movements and volumes of shares traded at that price to produce information for a graph depicting how a stock market has reacted to different types of movements. Another example can be when you have a database that holds archived data, and, every weekend the expired production data is placed in the archive database and a process is run to calculate stats on the historical data. Another scenario will be when a parameter coming into a stored procedure alters the basis of a join or a filter, which in turn, alters the execution plan wildly.

In all these situations, performance gains can be obtained if the stored procedure was recompiled before each execution. Keep in mind that these are stored procedures that process a heavy workload, but run very infrequently.

The syntax for adding WITH RECOMPILE is:

     CREATE PROCEDURE name @parameters datatype(datalength) WITH RECOMPILE 

Important

Use this option with care and only if it totally necessary for your solution. Using this option will slow down the execution of the stored procedure, as there will be a recompilation with each execution.

You can also manually recompile a stored procedure using the sp_recompile command. You will use this if you alter indexes on a table and do not expect a SQL Server recycle prior to the stored procedure being used. More on this subject will be covered in Chapter 2.