login

Subscribe to SQL Tutorial website RSS Feed
 » SQL Stored Procedure » Modifying and Compiling Stored Procecdure

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 statement guarantees 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 recompiled and adjusted to the current situation of the database which brings some advantages to the processing strategy. But be noted that the compilation process takes time and also decreases the performance of the database server. Therefore for each stored procedure ,we can specify which is the best to use WITH RECOMPILE.