数据字典解决方案和存储过程设计

ID        int           4 键号            TextEdit          
TNAME     varchar      20 数据表名          TextEdit          
ZDBB      varchar      20 字典版本          TextEdit          
FXH       int           4 序号            TextEdit          
FNAME     varchar      20 字段名称          TextEdit          
FTYPE     varchar      50 类型            TextEdit          
FLEN      int           4 长度            TextEdit          
FPREC     int           4 有效数字          TextEdit          
FSCALE    int           4 小数位数          TextEdit          
FMEMO     nvarchar     60 标题            TextEdit          
FZMC      nvarchar     20 分组名称          TextEdit          
SRLX      nvarchar     20 输入类型          TextEdit          
BYXM      bit           1 必要项目          CheckEdit         
HSXM      bit           1 整行项目          CheckEdit         
ALIGNMENT int           4 对齐            TextEdit          
EDIT      varchar      50 控件            TextEdit          
FORMATTYPE  varchar      50 格式类型          TextEdit          
FORMATSTR varchar     100 格式字符          TextEdit          
MASKTYPE  varchar      50 掩码类型          TextEdit          
MASKSTR   varchar     100 掩码字符          TextEdit          
SUMTYPE   varchar      20 摘要类型          TextEdit          
SUMFMT    varchar      30 摘要格式          TextEdit          
SEARCH    nvarchar    400 上网搜索          TextEdit          
TOOLTIP   nvarchar    240 操作提示          TextEdit      

CREATE PROCEDURE [dbo].[X9_ZRSJZD] 
   (@ZDBB VARCHAR(20),    
  @TBMC VARCHAR(20),    
  @SFGX INT)  
AS 
BEGIN TRY   
  SET NOCOUNT ON; 
  IF @SFGX=1
    BEGIN    
      --插入新行
      INSERT INTO X9_SJZD  (TNAME,ZDBB, FNAME, FXH, FTYPE,FLEN,FPREC,FSCALE)
        SELECT    OBJECT_NAME(OBJECT_ID) , @ZDBB AS ZDBB, NAME, COLUMN_ID, TYPE_NAME(SYSTEM_TYPE_ID) ,
            MAX_LENGTH ,PRECISION,SCALE
          FROM   SYS.COLUMNS WHERE  OBJECT_NAME(OBJECT_ID)=@TBMC AND NAME<>'X9STAMP' 
            AND ((OBJECT_NAME(OBJECT_ID)+@ZDBB+NAME) NOT IN  (SELECT TNAME+ZDBB+FNAME AS ZDHH 
            FROM  X9_SJZD AS X9_SJZD_1));
      --删除多余行
      DELETE FROM X9_SJZD WHERE TNAME=@TBMC AND FTYPE='BINARY';
      DELETE FROM X9_SJZD  WHERE TNAME=@TBMC AND  ((FNAME) NOT IN  
        (SELECT  NAME AS FNAME  FROM  SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID)=@TBMC));
    END
  IF @SFGX=2
    BEGIN    
      --删除所有行
      DELETE FROM X9_SJZD  WHERE TNAME=@TBMC AND ZDBB=@ZDBB;
      --插入所有行
      INSERT INTO X9_SJZD  (TNAME,ZDBB, FNAME, FXH, FTYPE,FLEN,FPREC,FSCALE)
        SELECT    OBJECT_NAME(OBJECT_ID) , @ZDBB AS ZDBB, NAME, COLUMN_ID, 
          TYPE_NAME(SYSTEM_TYPE_ID) ,MAX_LENGTH ,PRECISION,SCALE
          FROM   SYS.COLUMNS WHERE  OBJECT_NAME(OBJECT_ID)=@TBMC AND NAME<>'X9STAMP' 
            AND ((OBJECT_NAME(OBJECT_ID)+@ZDBB+NAME) NOT IN  
            (SELECT TNAME+ZDBB+FNAME AS ZDHH FROM  X9_SJZD AS X9_SJZD_1));
    END
  --更新相关信息   
  UPDATE X9_SJZD SET  FXH = SYS.COLUMNS.COLUMN_ID,  FTYPE = TYPE_NAME(SYSTEM_TYPE_ID),FLEN=MAX_LENGTH,FPREC=PRECISION,FSCALE=SCALE
    FROM X9_SJZD INNER JOIN      SYS.COLUMNS ON X9_SJZD.TNAME = OBJECT_NAME(SYS.COLUMNS.OBJECT_ID) AND 
      X9_SJZD.FNAME = SYS.COLUMNS.NAME   
    WHERE  TNAME =@TBMC AND ZDBB=@ZDBB ;   
  --设置缺省   
  UPDATE X9_SJZD SET FORMATTYPE=(CASE UPPER(FTYPE)  WHEN 'DECIMAL' THEN 'Numeric' WHEN 'NUMERIC' THEN 'Numeric'  
      WHEN 'DATETIME' THEN 'DateTime'  ELSE '' END), FORMATSTR =(CASE FTYPE  WHEN 'DECIMAL' THEN 'N2' 
      WHEN 'NUMERIC' THEN '0.'+REPLICATE ('0',FSCALE)   WHEN 'DATETIME' THEN 'YYYY-MM-DD'   ELSE '' END)  
    WHERE TNAME =@TBMC AND ZDBB=@ZDBB AND FORMATSTR =''  ;   
  UPDATE X9_SJZD SET SRLX=(CASE UPPER(FTYPE)  
      WHEN 'CHAR' THEN '文本'  WHEN 'NCHAR' THEN '文本'  WHEN 'TEXT' THEN '文本'  
      WHEN 'NTEXT' THEN '文本'   WHEN 'VARCHAR' THEN '文本'  WHEN 'NVARCHAR' THEN '文本'  
      WHEN 'INT' THEN '整型'  WHEN 'SMALLINT' THEN '整型'      WHEN 'BIGINT' THEN '整型'      
      WHEN 'DECIMAL' THEN '数值'    WHEN 'NUMERIC' THEN '数值'   WHEN 'FLOAT' THEN '数值'  WHEN 'REAL' THEN '数值'      
      WHEN 'DATE' THEN '日期时间'     WHEN 'DATETIME' THEN '日期时间' WHEN 'DATETIME2' THEN '日期时间'  
      WHEN 'SMALLDATETIME' THEN '日期时间'      WHEN 'DATETIMEOFSET' THEN '日期时间'  WHEN 'TIME' THEN '日期时间'  
      WHEN 'BIT' THEN '逻辑'   
      WHEN 'BINARY'  THEN '对象'  WHEN 'VARBINARY'  THEN '对象'  WHEN 'IMAGE' THEN '对象'      
        ELSE '' END)    
    WHERE  X9_SJZD.TNAME =@TBMC AND X9_SJZD.ZDBB=@ZDBB;   
  UPDATE X9_SJZD SET ALIGNMENT =5 WHERE (FNAME='ID' OR FNAME='OID' OR FNAME='MID' OR FNAME='MHH' OR EDIT='CheckEdit' ) 
      AND TNAME =@TBMC AND ZDBB=@ZDBB;   
  --更新为近似值 
  DECLARE @GXID INT;
  DECLARE @ZDID INT,@ZDFNAME VARCHAR(20),@ZDFTYPE VARCHAR(20),@ZDFLEN INT;
  DECLARE SJZD_cursor CURSOR LOCAL FOR
    SELECT ID,FNAME,FTYPE,FLEN FROM X9_SJZD 
    WHERE TNAME =@TBMC AND ZDBB=@ZDBB AND (FMEMO='' OR EDIT='');
  OPEN SJZD_cursor;
  FETCH FROM SJZD_cursor
    INTO @ZDID,@ZDFNAME,@ZDFTYPE,@ZDFLEN;
  WHILE @@FETCH_STATUS=0
    BEGIN
      SET @GXID=0;
      SELECT TOP 1 @GXID=ID FROM X9_SJZD 
        WHERE TNAME=@TBMC AND FNAME=@ZDFNAME AND FTYPE=@ZDFTYPE AND 
          FLEN=@ZDFLEN AND FMEMO<>'' AND EDIT<>'' ORDER BY ID DESC;
      IF @GXID=0
        BEGIN
          SELECT TOP 1 @GXID=ID FROM X9_SJZD 
            WHERE FNAME=@ZDFNAME AND FTYPE=@ZDFTYPE AND 
              FLEN=@ZDFLEN AND FMEMO<>'' AND EDIT<>'' ORDER BY ID DESC;
        END
      IF @GXID<>0
        BEGIN
          UPDATE X9_SJZD SET FMEMO = ZD1.FMEMO, FZMC = ZD1.FZMC, SRLX = ZD1.SRLX,BYXM = ZD1.BYXM, 
            HSXM=ZD1.HSXM,ALIGNMENT=ZD1.ALIGNMENT, EDIT = ZD1.EDIT,MASKTYPE  = ZD1.MASKTYPE ,
            MASKSTR  = ZD1.MASKSTR , FORMATTYPE = ZD1.FORMATTYPE,FORMATSTR = ZD1.FORMATSTR,
            SUMTYPE = ZD1.SUMTYPE,SUMFMT = ZD1.SUMFMT,SEARCH = ZD1.SEARCH,TOOLTIP = ZD1.TOOLTIP    
          FROM X9_SJZD INNER JOIN  X9_SJZD AS ZD1 ON ZD1.ID=@GXID 
          WHERE X9_SJZD.ID =@ZDID;   
        END
    FETCH NEXT FROM SJZD_cursor
      INTO @ZDID,@ZDFNAME,@ZDFTYPE,@ZDFLEN;
  END
  CLOSE SJZD_cursor;
  DEALLOCATE SJZD_cursor;
  --设置功能定义用控件
  UPDATE X9_SJZD SET EDIT=(CASE WHEN ZDBB='99' AND FMEMO LIKE '%字段%' THEN 'FieldList' ELSE 'TextEdit' END)         
    WHERE X9_SJZD.ZDBB=@ZDBB AND X9_SJZD.TNAME =@TBMC AND (EDIT IS NULL OR EDIT ='');   
  --设置分组
  UPDATE X9_SJZD SET FZMC='1.基本信息'    
    WHERE X9_SJZD.ZDBB=@ZDBB AND X9_SJZD.TNAME =@TBMC AND (X9_SJZD.FZMC ='' OR X9_SJZD.FZMC IS NULL);   
  UPDATE X9_SJZD SET EDIT='CheckEdit'  
    WHERE X9_SJZD.ZDBB=@ZDBB AND X9_SJZD.TNAME =@TBMC AND EDIT='TextEdit' AND FTYPE='bit';
  SELECT '数据字典维护';  
END TRY  
BEGIN CATCH  
  DECLARE @ERRORMESSAGE NVARCHAR(4000),@ERRORSEVERITY INT,@ERRORSTATE INT;    
  SELECT  @ERRORMESSAGE = ERROR_MESSAGE(),@ERRORSEVERITY = ERROR_SEVERITY(),@ERRORSTATE = ERROR_STATE();    
  RAISERROR (@ERRORMESSAGE,  @ERRORSEVERITY,  @ERRORSTATE );   
END CATCH;