Modifying and Compiling Stored Procecdure

Once a stored procedure is resided in the database server catalog, we can modify it by using the ALTER PROCEDURE statement as follows:

	ALTER PROCEDURE spName
		(parameter_list)
	AS
	BEGIN
		-- stored procedure body here
	END

To remove a stored procedure from database catalog, we can use DROP PROCEDURE

	DROP PROCEDURE spName

When you remove stored procedure, be noted that some database products have a feature that allows you to remove the dependency database objects like table, view, index or other stored procedures also.

Stored procedure is compiled before it executes. Each database server has its own compiling strategy. You can specify the compiling option when you with WITH RECOMPILE statement as follows:

CREATE PROCEDURE spName
   (parameter_list) AS
   WITH RECOMPILE
BEGIN
	-- stored procedure body here
END

The WITH RECOMPILE guarantee that each time the stored procedure is invoked, the compiler is called and compile the stored procedure again. With WITH RECOMPILER the stored procedure is recompile and adjusted to the current situation of the database which brings some advantages to the processing strategy. But the compilation takes time and also decrease the performance. Therefore for each stored procedure we can specify which is the best to use WITH RECOMPILE.