SQL: 打印存储过程

samrv / 2023-08-15 / 原文

打印SAP business one 9.2 存储过程代码

use SAPB1  -- SAP BUSINESS ONE 9.2 
GO 

DECLARE C_SP CURSOR  FOR 
 SELECT OBJ.NAME AS STORE_PROCEDURE,
       CASE WHEN RS.DEFINITION IS NULL THEN N'加密'
       ELSE N'未加密' END AS [IS_ENCRYPT],
       RS.DEFINITION ,
       LEN( RS.definition) AS STRING_LONG
  FROM SYS.all_objects OBJ,
       SYS.ALL_SQL_MODULES RS 
 WHERE OBJ.object_id = RS.object_id
 AND OBJ.type = 'P'
  AND OBJ.name IN ('TmSp_UpgradeElectronicInvoiceMX')
 order by obj.name ;
DECLARE @STORE_PROCEDURE NVARCHAR(128),
 @IS_ENCRYPT NVARCHAR(20),  
 @DEFINITION NVARCHAR(MAX),
 @STRING_LONG INT; -- 第4000字段一段;  
declare @times int, --  数 
 @i int ,
 @j int ,
 @start int;   
BEGIN
-- SQL SERVER PRINT打印函数 每次最多只支持4000BYTE. 故打印前先对内容进行分段。 
  OPEN C_SP ;
  FETCH NEXT FROM C_SP INTO  @STORE_PROCEDURE, @IS_ENCRYPT, @DEFINITION,@string_long
  WHILE @@FETCH_STATUS = 0 
  BEGIN
    SET @times = CEILING(@STRING_LONG/4000.0);
    set @i= 1;
    IF @IS_ENCRYPT =N'未加密'
	BEGIN
     WHILE @i <=@times
     begin
       set @start = 1+ (@i-1)*4000.0;
       print Substring(@definition,@start,4000.0)
       set @i= @i+1;
     end; 
	 PRINT ''
	 print 'GO'+ CHAR(10)
	 END;
	 
     /* 
    begin
      if @STRING_LONG>4000 and @STRING_LONG<8000 
      begin
        print substring(@definition,1,4000)
        print subString(@definition,4001,8000)
      end  
      else  
        print @definition;        
      print 'GO'
    end;
    */
   FETCH NEXT FROM C_SP INTO  @STORE_PROCEDURE, @IS_ENCRYPT, @DEFINITION,@string_long
  END;
  CLOSE C_SP;
  DEALLOCATE C_SP;
END;