DDL statement(schema changes) will degrade Stored Procedure

The main use of stored procedure is:

• The written code in stored procedure is precompiled and it can be executed from Execution plan stored in system cached objects.
• The code with DDL statement(schema change) in a stored procedure will not be stored in Cache for reuse.

Note: It will not throw any error, but performance will degrade.

 Below  example will elaborate this type of scenario:

Create proc test1

as
begin
select 1 as no into #temp123
--Alter table #temp123 add no1 int
end

go

EXEC test1
go

Create proc test2

as
begin
select 1 as no into #temp123
Alter table #temp123 add no1 int
end

go
EXEC test2
go

select * from sys.syscacheobjects where sql like '%test%' and objtype='Proc'

go

Drop proc test1,test2

The different between the two SP is “Test2 has DDL statement in it”

When we run the above script we can get

We can find the SP using the below statement:

select object_name(id),* from syscomments where text like '%ALTER TABLE%'

Please let me know for any clarification.