:SetVar Procedure "AUX:DropProcedures"
:r $(DropProcedureScript_AUX)
create procedure [$(Procedure)] @NamePattern       $(dt_String_AUX),
                                @ExceptionPattern  $(dt_String_AUX) = null
AS
/*==================================================================================================
# SQLAUX scripting library | Author: Sergei Y.Kitáev
----------------------------------------------------------------------------------------------------
#
# STORED PROCEDURE SET DELETION ROUTINE (BY NAME PATTERN, OF EXISTING PROCEDURE OBJECTS):
#
# === Usage: ===
# exec [AUX:DropProcedures] N'<Prefix token>:%'
# exec [AUX:DropProcedures] N'<Prefix token>::%'
# exec [AUX:DropProcedures] N'<Prefix token>:%', N'<Prefix token>::%'
# exec [AUX:DropProcedures] N'<Prefix token>\_%'
#
# ( Character “\” serves as “escape” for indication,
#   that “_” symbol is not used as placeholder. )
#
# *** CAUTION: ***
# USE THIS PROCEDURE WITH HIGH CAUTION
# TO AVOID UNINTENDED REMOVING OF PROCEDURE OBJECTS!
#
==================================================================================================*/
$(BEGIN_AUX)
----------------------------------------------------------------------------------------------------

if @NamePattern is null
   exec [AUX:RaiseIncorrectProcedureCall] '$(Procedure)'

if CHARINDEX(' ',@NamePattern)>0 or
   @NamePattern='%' or SUBSTRING(@NamePattern,1,2)='%%'
   exec [AUX:RaiseError] '$(Procedure)',
      'Incorrect pattern for procedure name.',
      N'Некорректный шаблон имени процедуры.'

----------------------------------------------------------------------------------------------------

declare @PROCEDURE_LIST table ([Name] sysname primary key)
insert @PROCEDURE_LIST
   select [name] from [sysobjects] (nolock)
   where [type]='P' and [name] like @NamePattern escape $(BACKSLASH_AUX) and
         ( @ExceptionPattern is null or [name] not like @ExceptionPattern escape $(BACKSLASH_AUX) )

----------------------------------------------------------------------------------------------------
$(BEGIN_TRANSACTION_AUX)
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
$(LOOP_AUX)
begin
   declare @ProcedureName sysname
   select @ProcedureName = MIN([Name]) from @PROCEDURE_LIST
   if @ProcedureName is null BREAK

   exec( 'drop procedure ['+@ProcedureName+']' )

   delete @PROCEDURE_LIST where [Name] = @ProcedureName
end
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
$(COMMIT_TRANSACTION_AUX)
----------------------------------------------------------------------------------------------------
$(END_AUX)
/*================================================================================================*/
GO
