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.
>Solution :
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 the minimum amount of text. The content is then filled in with the ALTER.
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.