Back to OIM Explorer

dbo.QBM_PFunctionDrop

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.834 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_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL123 lines
1CREATE PROCEDURE QBM_PFunctionDrop(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('FN', 'IF', 'TF'))28    BEGIN29      SELECT30        @SQLcmd = CONCAT('drop function if exists dbo.',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 function 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 'function' 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('QBM_FGIObjectIsDropable',79    'QBM_FSQObjectComment' collate database_default)80    SELECT @ElementCount = @@ROWCOUNT81    IF EXISTS(82      SELECT TOP 1 183      FROM @ElementBuffer e84      WHERE85        len(e.LongIdent1) > 30 AND @force = 0)86    BEGIN87      SELECT TOP 1 @name = e.LongIdent188      FROM @ElementBuffer e89      WHERE90        len(e.LongIdent1) > 3091      SELECT92        @LengthMessage = CONCAT(@LengthMessage,93        @name,94        '|')95      RAISERROR(@LengthMessage,96      18,97      1)98        WITH nowait99    END100    SELECT @ElementIndex = 1101    WHILE @ElementIndex <= @ElementCount102    BEGIN103      SELECT TOP 1 @SQLcmd = bu.ContentFull104      FROM @ElementBuffer bu105      WHERE106        bu.ElementIndex = @ElementIndex107      IF @DebugSwitch > 0108      BEGIN109        print @sqlcmd110      END111      EXEC sp_executeSQL @SQLcmd112      SELECT @ElementIndex += 1113    END114    verarbeitet:115  END TRY116  BEGIN CATCH117    EXEC QBM_PSessionErrorAdd DEFAULT118    RAISERROR('',119    18,120    1)121      WITH NOWAIT122  END CATCH123END
Open raw exported source
SQL ยท Raw19 lines
1  create   procedure QBM_PFunctionDrop (@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( 'FN'  , 'IF'  , 'TF'  ) ) begin select @SQLcmd = CONCAT('drop function if exists dbo.'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 function 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'function' 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 ('QBM_FGIObjectIsDropable', 'QBM_FSQObjectComment'14 collate database_default) select @ElementCount = @@ROWCOUNT if exists (select top 1 1 from @ElementBuffer e where len(e.LongIdent1) > 30 and @force = 150 ) begin select top 1 @name = e.LongIdent1 from @ElementBuffer e where len(e.LongIdent1) > 30 select @LengthMessage = concat(@LengthMessage , @name , 16'|') raiserror(@LengthMessage, 18, 1) with nowait end    select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLcmd = bu.ContentFull17 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @sqlcmd end exec sp_executeSQL @SQLcmd select @ElementIndex18 += 1 end verarbeitet: END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 19