You know that Create or alter procedure statement should be always in a seperate batch otherwise you will get an error "'CREATE/ALTER PROCEDURE' must be the first statement in a query batch." Sometime when you want to create or alter the procedure based on the existance of a procedure you may wonder why the following is not working
if exists
(
select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME='testing'
)
alter procedure testing
(
@i int
)
as
select @i as number
else
create procedure testing
(
@j int
)
as
select @j as number
When you execute the above you get the error
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'procedure'.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@i".
Msg 111, Level 15, State 1, Line 15
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
It is becuase CREATE or ALTER procedure statement should always be executed as seperate batch. Now if you want to simulate the above logic, only way is to make use of the dynamic sql as shown below
if exists
(
select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME='testing'
)
exec(
'alter procedure testing
(
@i int
)
as
select @i as number
')
else
exec(
'create procedure testing
(
@j int
)
as
select @j as number
')