Programming in MS SQL Server 2012

WITH RECOMPILE Option

Stored 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

IF EXISTS (SELECT *

FROM sysobjects

WHERE name = N'dbo.usp_EmployeePhoneListRecompile' AND type = 'P')

DROP PROCEDURE dbo.usp_EmployeePhoneListRecompile

CREATE PROCEDURE dbo.usp_EmployeePhoneListRecompile

@Phone varchar(50)

WITH RECOMPILE

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

Query Output Screen

The following Transact-SQL statements are used to retrieve employee(s) information based on given input parameter unique pattern data values.

-- Example 119 --

EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '%'

EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '10%'

EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '104%'

Query Output Screen

The WITH RECOMPILE keyword forces a recompile of a stored procedure the next time it is run. The following Transact-SQL statements are used to retrieve employee(s) information based on given input parameter unique pattern data values.

Query Output Screen

The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run. The following Transact-SQL statements are used to retrieve employee(s) information based on given input parameter unique pattern data values.

-- 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'

Query Output Screen

* * * * *

Your Comment To AUTHOR