Back to OIM Explorer

dbo.QBM_PProcedureDrop

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.100 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FGIObjectIsDropable source text reference
  • references source dbo.QBM_FSQObjectComment source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL143 lines
1CREATE PROCEDURE QBM_PProcedureDrop(2  @pattern nvarchar(255),3  @force BIT = 04)5AS6BEGIN7  DECLARE @SQLcmd nvarchar(max)8  DECLARE @name nvarchar(255)9  DECLARE @LengthMessage nvarchar(256) = '#LDS#Name of Element {0} is too long (30).|'10  DECLARE @ElementBuffer TABLE(ElementIndex int identity,11  Int1 int DEFAULT 0,12  ContentShort nvarchar(400) collate database_default,13  LongIdent1 nvarchar(256) collate database_default,14  LongIdent2 nvarchar(256) collate database_default,15  ContentFull nvarchar(max) collate database_default)16  DECLARE @ElementCount int17  DECLARE @ElementIndex int18  DECLARE @crlf nvarchar(16) = nchar(13) + nchar(10)19  DECLARE @CommentCode BIT = 020  DECLARE @DebugSwitch int = 021  SET XACT_ABORT OFF22  BEGIN TRY23    IF @pattern NOT LIKE '%[%]%' AND @force = 0 AND EXISTS(24      SELECT TOP 1 125      FROM sys.objects o26      WHERE27        o.name = @pattern AND o.type IN('P'))28    BEGIN29      SELECT30        @SQLcmd = CONCAT('drop procedure if exists ',31        @pattern)32      EXEC sp_executesql @SQLCmd33      GOTO verarbeitet34    END35    IF @@NESTLEVEL < 236    BEGIN37      IF EXISTS(38        SELECT TOP 1 139        FROM sys.tables t40        WHERE41          t.name = 'QBMModuleDef')42      BEGIN43        IF EXISTS(44          SELECT TOP 1 145          FROM QBMModuleDef d46          WHERE47            d.UID_ModuleDef = 'MDK-Moduledefinition') AND EXISTS(48        SELECT TOP 1 149        FROM information_schema.routines r50        WHERE51          r.ROUTINE_type = N 'function' AND r.ROUTINE_NAME = 'QBM_FSQObjectComment')52        BEGIN53          SELECT @CommentCode = 154        END55      END56    END57    INSERT INTO @ElementBuffer(LongIdent1,58    LongIdent2,59    ContentFull)60    SELECT61      r.ROUTINE_NAME,62      r.ROUTINE_SCHEMA,63      CONCAT(CASE @CommentCode64      WHEN 1 THEN65      CONCAT('print dbo.QBM_FSQObjectComment (''', r.ROUTINE_NAME, ''')', @crlf)66    ELSE ''67    END,68    'drop procedure if exists "',69    r.ROUTINE_SCHEMA,70    N '"."',71    r.ROUTINE_NAME,72    N '"')73    FROM information_schema.routines r74    JOIN sys.schemas s75      ON r.ROUTINE_SCHEMA = s.name collate database_default76    WHERE77      r.ROUTINE_type = N 'procedure' collate database_default AND dbo.QBM_FGIObjectIsDropable(s.schema_id) = 1 AND r.ROUTINE_NAME LIKE @pattern78    collate database_default AND r.ROUTINE_NAME NOT IN(N 'QBM_PProcedureDrop' collate database_default,79    N 'QBM_PSessionErrorAdd',80    N 'QBM_PSessionContextSet')81    SELECT @ElementCount = @@ROWCOUNT82    IF EXISTS(83      SELECT TOP 1 184      FROM @ElementBuffer e85      WHERE86        len(e.LongIdent1) > 30 AND @force = 0)87    BEGIN88      SELECT TOP 1 @name = e.LongIdent189      FROM @ElementBuffer e90      WHERE91        len(e.LongIdent1) > 3092      SELECT93        @LengthMessage = CONCAT(@LengthMessage,94        @name,95        '|')96      RAISERROR(@LengthMessage,97      18,98      1)99        WITH nowait100    END101    SELECT @ElementIndex = 1102    WHILE @ElementIndex <= @ElementCount103    BEGIN104      SELECT TOP 1 @SQLcmd = bu.ContentFull105      FROM @ElementBuffer bu106      WHERE107        bu.ElementIndex = @ElementIndex108      IF @DebugSwitch > 0109      BEGIN110        print @sqlcmd111      END112      EXEC sp_executeSQL @SQLcmd113      SELECT @ElementIndex += 1114    END115    verarbeitet:116  END TRY117  BEGIN CATCH118    DECLARE @Message nvarchar(4000)119    IF EXISTS(120      SELECT TOP 1 1121      FROM sys.objects o122      WHERE123        o.name = 'QBM_PSessionErrorAdd')124    BEGIN125      EXEC QBM_PSessionErrorAdd DEFAULT,126        @SQLcmd127      RAISERROR('',128      18,129      1)130        WITH NOWAIT131    END132    ELSE133    BEGIN134      SELECT135        @Message = isnull(ERROR_MESSAGE(),136        '<no message>')137      RAISERROR(@Message,138      18,139      1)140        WITH NOWAIT141    END142  END CATCH143END
Open raw exported source
SQL ยท Raw21 lines
1 create   procedure QBM_PProcedureDrop (@pattern nvarchar(255) , @force bit = 0  )  as begin  declare @SQLcmd nvarchar(max) declare @name nvarchar2(255)  declare @LengthMessage nvarchar(256) = '#LDS#Name of Element {0} is too long (30).|' declare @ElementBuffer     table (ElementIndex int identity3 , Int1 int default 0 , ContentShort nvarchar(400) collate database_default , LongIdent1 nvarchar(256) collate database_default , LongIdent2 nvarchar(2564) collate database_default , ContentFull nvarchar(max) collate database_default ) declare @ElementCount int declare @ElementIndex int declare @crlf nvarchar5(16) = nchar(13) + nchar(10) declare @CommentCode bit = 0 declare @DebugSwitch int = 0 SET XACT_ABORT OFF BEGIN TRY if @pattern not like '%[%]%' and @force6 = 0 and exists ( select top 1 1 from sys.objects o where o.name = @pattern and o.type in( 'P'  ) ) begin select @SQLcmd = CONCAT('drop procedure if exists '7, @pattern) exec sp_executesql @SQLCmd  goto verarbeitet end if @@NESTLEVEL < 2  begin if exists (select top 1 1 from sys.tables t where t.name = 'QBMModuleDef'8 ) begin if exists (select top 1 1 from QBMModuleDef d where d.UID_ModuleDef = 'MDK-Moduledefinition' ) and exists (select top 1 1 from information_schema.routines9 r where r.ROUTINE_type = N'function' and r.ROUTINE_NAME = 'QBM_FSQObjectComment' ) begin select @CommentCode = 1 end end end insert into @ElementBuffer10(LongIdent1, LongIdent2, ContentFull) select r.ROUTINE_NAME , r.ROUTINE_SCHEMA, concat( case @CommentCode when 1 then concat('print dbo.QBM_FSQObjectComment ('''11 , r.ROUTINE_NAME , ''')' , @crlf ) else '' end , 'drop procedure if exists "' , r.ROUTINE_SCHEMA, N'"."' , r.ROUTINE_NAME , N'"' )  from information_schema.routines12 r join sys.schemas s on r.ROUTINE_SCHEMA = s.name collate database_default where r.ROUTINE_type = N'procedure' collate database_default and dbo.QBM_FGIObjectIsDropable13(s.schema_id) = 1 and r.ROUTINE_NAME like @pattern collate database_default and r.ROUTINE_NAME not in ( N'QBM_PProcedureDrop' collate database_default 14  , N'QBM_PSessionErrorAdd' , N'QBM_PSessionContextSet' ) select @ElementCount = @@ROWCOUNT if exists (select top 1 1 from @ElementBuffer e where len(e.LongIdent115) > 30 and @force = 0 ) begin select top 1 @name = e.LongIdent1 from @ElementBuffer e where len(e.LongIdent1) > 30 select @LengthMessage = concat(@LengthMessage16 , @name , '|') raiserror(@LengthMessage, 18, 1) with nowait end select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLcmd17 = bu.ContentFull from @ElementBuffer bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @sqlcmd end exec sp_executeSQL @SQLcmd select18 @ElementIndex += 1 end verarbeitet: END TRY BEGIN CATCH declare @Message nvarchar(4000)  if exists (select top 1 1 from sys.objects o where o.name = 'QBM_PSessionErrorAdd'19 ) begin exec QBM_PSessionErrorAdd default, @SQLcmd RAISERROR ('', 18, 1) WITH NOWAIT end else begin  select @Message = isnull(ERROR_MESSAGE() , '<no message>'20) RAISERROR (@Message, 18, 1) WITH NOWAIT end END CATCH end 21