--:SetVar Procedure "AUX:DropProcedures"
if exists( select * from [sysobjects] (nolock) where [type]='P' and [name]=N'AUX:DropProcedures' )
   drop procedure [AUX:DropProcedures]
GO
/* Translated by $SQLTRANS utility (SQL-file technology). Method author: Sergei Y.Kitáev.
** Script translation date-time: 2022-11-27 00:29:29.24 (stamp of SQL-file generation). */
create procedure [AUX:DropProcedures] @NamePattern       nvarchar(max),
                                @ExceptionPattern  nvarchar(max) = 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 begin try set xact_abort off; set ansi_nulls,ansi_null_dflt_on,quoted_identifier,arithabort,nocount on; set implicit_transactions off; declare @initial_tran_count_aux int, @is_local_tran_open_aux bit; select @initial_tran_count_aux=@@trancount, @is_local_tran_open_aux=0; end try begin catch throw; end catch; begin try
----------------------------------------------------------------------------------------------------

if @NamePattern is null
   exec [AUX:RaiseIncorrectProcedureCall] 'AUX:DropProcedures'

if CHARINDEX(' ',@NamePattern)>0 or
   @NamePattern='%' or SUBSTRING(@NamePattern,1,2)='%%'
   exec [AUX:RaiseError] 'AUX:DropProcedures',
      '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 '\' and
         ( @ExceptionPattern is null or [name] not like @ExceptionPattern escape '\' )

----------------------------------------------------------------------------------------------------
begin if @is_local_tran_open_aux=1 raiserror ('Local transaction nesting is forbidden. (Macro: BEGIN_TRANSACTION_AUX)',11,1); begin transaction; set @is_local_tran_open_aux=1; end
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
while 0=0
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
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
begin if @is_local_tran_open_aux=0 raiserror ('Open local transaction is absent. (Macro: COMMIT_TRANSACTION_AUX)',11,1); commit transaction; set @is_local_tran_open_aux=0; end
----------------------------------------------------------------------------------------------------
if @is_local_tran_open_aux=1 raiserror ('Not closed local transaction was detected, previously opened by BEGIN_TRANSACTION_AUX macro.',11,1); end try begin catch if @is_local_tran_open_aux=1 and (@@trancount>@initial_tran_count_aux or xact_state()=-1) rollback transaction; set @is_local_tran_open_aux=0; throw; end catch; end
/*================================================================================================*/
GO
