Programming in MS SQL Server 2012 |
WITH RECOMPILE OptionStored Procedure is a group of Transact-SQL statements that compiled into a single query execution plan based on database structure and predefined stored procedure's parameter(s) information. By default, SQL Server automatically recompiling existing stored procedures when restarted server or update referenced table structure of stored procedure. If parameter values on the stored procedure are frequently atypical, forcing a recompile of the stored procedure that creates a new query execution plan based on different parameter values to improve stored procedure performance. SQL Server provides three ways to recompile a stored procedure: 01. WITH RECOMPILE statement is used within stored procedure itself. 02. RECOMPILE keyword is used when execute user-defined stored procedure. 03. sp_recompile system stored procedure is used when execute user-defined stored procedure. The following example describes CREATE PROCEDURE including WITH RECOMPILE Option in the parameter section. This option causes the stored procedure to execute more slowly, because the stored procedure must be recompiled each time it is executed. --
Example 118 -- USE EBusiness GO
IF EXISTS (SELECT *
FROM
sysobjects
WHERE
name = N'dbo.usp_EmployeePhoneListRecompile' AND type = 'P')
DROP
PROCEDURE dbo.usp_EmployeePhoneListRecompile GO CREATE PROCEDURE dbo.usp_EmployeePhoneListRecompile @Phone varchar(50) WITH RECOMPILE AS SELECT
EMP.Emp_Code,
EMP.Emp_Name,
EMP.Phone,
EMP.Dept_Code,
DEPT.Dept_Name FROM EMP INNER JOIN DEPT ON EMP.Dept_Code = DEPT.Dept_Code WHERE EMP.Phone LIKE @Phone --
Example 119 -- EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '%' EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '10%' EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '104%' --
Example 120 -- EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '%' WITH RECOMPILE EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '10%' WITH RECOMPILE EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '104%' WITH RECOMPILE --
Example 121 -- EXECUTE sp_recompile N'EBusiness.dbo.usp_EmployeePhoneListRecompile' |
* * * * *