| How can we recompile a stored procedure at run time in SQL Server?
Below are the different methods to recompile a store procedure at runtime in SQL Server:-
(1)Below we have put parameter value as 10 and use keyword as RECOMPILE
EXEC dbo.Proc_name @parameter1=10 WITH RECOMPILE;
GO
(2)By using sp_recompile system store procedure
EXEC sp_recompile 'Person.Address';
GO
(3)By using RECOMPILE hints keywords in stored procedure headers
ALTER PROCEDURE dbo.Proc_name
@parameter1 int
WITH RECOMPILE
AS
SELECT statements
EXEC dbo.Proc2 @parameter1;
GO
(4)By adding RECOMPILE with select statement in store procedure
ALTER PROCEDURE dbo.Proc_name
@parameter1 int
AS
SELECT statement where clause OPTION (RECOMPILE);
EXEC dbo.Proc2 @parameter1;
GO | | |