TSQL Alter procedure if it exists - syntax error
sql, sql-server, stored-procedures
Solution
I'm not sure what you mean by this statement:
I have read that the CREATE or ALTER must be the first statement in a query block, but I don't understand why.
You are correct that these need to be the first statements in a batch. That is a property of the T-SQL language -- not something whose cause needs to be understood but something that you need to know to use the language properly. Typically, the structure to do what you want in SQL Server is:
IF (OBJECT_ID('..sp_cake', 'P') is not null)
BEGIN
DROP PROCEDURE dbo.sp_code
END;
GO
CREATE PROC [dbo].[sp_cake] as
BEGIN
select 1
END;
I do agree that it would be nice to have a `create procedure if not exists` or `create or alter procedure`. Getting that functionality requires lobbying Microsoft.
Problem
I'm trying to understand what specifically is happening in SQL that means the following syntax is not allowed (and I'm finding it hard to search for): ``` IF (OBJECT_ID('..sp_cake', 'P') is not null) ALTER PROC sp_cake as select 1 ``` I would expect the ALTER to be valid because T-SQL is wrapping it up in its own BEGIN-END block and nothing bad could happen with the rest of the script block. This is what T-SQL is doing, wrapping everything up and keeping it cleanly separated: ``` IF (OBJECT_ID('..sp_cake', 'P') is not null) BEGIN ALTER PROC [dbo].[sp_cake] as BEGIN select 1 END END ``` And these examples would be the simplest expression of what I think I'm doing (and these are syntactically correct) ``` IF (OBJECT_ID('..sp_cake', 'P') is not null) select 1 IF (OBJECT_ID('..sp_cake', 'P') is null) select 1 -- i.e. this works and 1 is the output ``` I have read that the CREATE or ALTER must be the first statement in a query block, but I don't understand why. I know that I can get around this problem by either: - creating a dummy sproc and then altering it outside of an IF block, or; - creating a string of the entire sproc and executing it as a statement; but I don't see why it is not valid to test for existence and then ALTER.