How to Recompile Stored Procedure at Run Time?

We can Recompile Stored Procedure in two ways.

Option 1:

CREATE PROCEDURE dbo.PersonAge(@MinAge INT, @MaxAge INT)
WITH RECOMPILE
AS
SELECT*
FROM dbo.tblPerson
WHERE Age <= @MinAge AND Age >= @MaxAge
GO

Option 2:

EXEC dbo.PersonAge65, 70 WITHRECOMPILE

We can use RECOMPILE hint with a query and recompile only that particular query. However, if the parameters are used in many statements in the stored procedure and we want to recompile all the statements, then instead of using the RECOMPILE option with all the queries, we have one better option that uses WITH RECOMPILE during stored procedure creation or execution.

This method is not recommended for large stored procedures because the recompilation of so many statements may outweigh the benefit of a better execution plan.