What is "dummy" in CREATE PROCEDURE statement-CodePudding

I'm doing investigation of code repo and find one thing that make me confused. SQL Server stored procedures are contained in a repo as a set of queries with following structure:

IF OBJECT_ID(N'[dbo].[sp_ProcTitle]', N'P') IS NULL
BEGIN
    EXEC dbo.sp_executeSQL N'CREATE PROCEDURE [dbo].[sp_ProcTitle] AS dummy:;';
END

ALTER PROCEDURE dbo.sp_ProcTitle 
    @ParamOne int, 
    @ParamTwo date, 
    @ParamThree int
AS
    SET NOCOUNT ON

     -- some procedure body

END

Never before I saw AS dummy:; and now I'm a little confused, I can't find any good explanation what is it and how it works. Could anybody tell me what does it mean this statement? How it works? What is the reason to have it? Any thought would be good to hear. Or, please, advise me some link where I can find good explanation.

CodePudding user response：

This is simply a label, such that could be used in a GOTO statement.

The word "dummy" is unimportant. It's simply trying to create the stored procedure if it doesn't exist, with a minimal amount of text. The content is then filled in with the ALTER.

Conceivably, the dummy text could later be searched for to see if any procedures were created and didn't have their content filled in, to check against failed deployments, etc.

Why do this? Well, it preserve the creation time of the stored procedure in metadata (which can be useful in administration or tracking down problems), and is compatible with versions of SQL Server that lack the CREATE OR ALTER... support.

CodePudding user response：

This might make a little more sense if we add a little formatting to the CREATE:

CREATE PROCEDURE [dbo].[sp_ProcTitle]
AS
    dummy:

This is, effectively, an empty procedure with a label called dummy. The user appears to be using this to ensure that the procedure exists first, and the ALTERing it. In older versions of SQL Server, such methods were needed because it didn't support CREATE OR ALTER syntax. As such, if you tried to ALTER a procedure that didn't exist the statement failed, and likewise if you try to CREATE a procedure that already exists it fails.

If you are on a recent version of SQL Server, I'd suggest changing to CREATE OR ALTER and getting rid of the call to sys.sp_executesql.