高分求 SQL SERVER编码规则调整 函数错误

Question

--请教如何函数，错在那里
--注：学邹建
--树型数据之调整编号规则（通用函数+存储过程）
--由于调整编号规则，调整编码的隶属关系和移动复制节点都涉及到调整规则，则写一个通用函数
ALTER function dbo.ChangeCodeRule   -- dbo.ChangeCodeRule('2,2,3','2,4,2','hzfa','flbh','0',0)
  (@old_code_rule nvarchar(20),      --老编号规则
   @new_code_rule nvarchar(20),      --新编号规则
   @Table_Name nvarchar(20),    --编号表表名
   @Field_Name nvarchar(20),    --编号表编号字段名
   @Filled_Char nvarchar(2),       --扩展编号时填充的字符
   @Position int                --扩展和收缩编码时，从那个位置开个收扩
  ) 
  returns nvarchar(4000)
  as
  begin
  --第一步:将新老编号拆分表编码表，以备后用
    --1、创建编码规则表（老）--2,3,4
    declare @old_table_rule table(id int identity(1,1),Code_Len int,Code_Lens int,Code_Char nvarchar(100)) 
    declare @code_len int,
            @code_lens varchar(10),
            @code_char nvarchar(100)
    set @old_code_rule=@old_code_rule+N','
    set @code_lens=1
    while @old_code_rule>''
      begin
        set @code_len=convert(int,left(@old_code_rule,charindex(',',@old_code_rule)-1))
        set @old_code_rule=stuff(@old_code_rule,1,charindex(',',@old_code_rule),N'')
        set @Code_Char=N'substring('+@Field_Name+N','+@code_lens+N','+@code_len+N')'
               insert @old_table_rule(code_len,code_lens,code_char)
                 select @code_len,@code_lens,@code_char
              set @code_lens=@code_lens+@code_len
      end
      --创建编号规则表（新）
    declare @new_table_rule table(id int identity(1,1),code_len int,code_lens int)
    set @new_code_rule=@new_code_rule+','
    set @code_lens=0
    while @new_code_rule>''
      begin
        set @code_len=convert(int,left(@new_code_rule,charindex(',',@new_code_rule)-1))
        set @new_code_rule=stuff(@new_code_rule,1,charindex(',',@new_code_rule),N'')
        set @code_lens=@code_lens+@code_len
              insert @new_table_rule(code_len,code_lens)
                select @code_len,@code_lens
      end
    --重排编号动态T-SQL
    declare @sql nvarchar(4000)
    select @sql=case 
                  when n.code_len=0 then N''                                           --如果新编号长度为0，表示去掉此段代码
                 else+N'
                  case when len('+@field_name+N')<o.code_len then N''''                          
                    else'+                            
                      case when n.code_len=o.code_len then o.code_char                   --若新老编号长度相同，则不需要特殊处理，取原值 
                           when n.code_len>o.code_len then                               --若新编号长度大于老编号长度，则继续判断位置
                             case when @Position=-1 or @position<n.code_len            --此处表示表后面开始填充指定字符
                                  then o.code_char+N'+N'+quotename(replicate(@Filled_Char,n.code_len-o.code_len),N'''')
                               else N'stuff('+o.code_char+N','+cast(@position+1 as int)+N',0'+quotename(replicate(@Filled_Char,n.code_len-o.code_len),N'''')+N')'
                             end   
                      else  --收缩
                             case when @Position=-1 or @position>o.code_len  
                                  then+N'left('+o.code_char+N','+n.code_len-o.code_len+N')'
                               else+ N'stuff('+o.code_char+N','+cast(@position+1 as int)+N','+n.code_len-o.code_len+N','+N''''+N')'
                             end     
                      end
                +N'end'
              end
      from @new_table_rule N,@old_table_rule O
        where n.id=o.id
        
      return(stuff(@sql,1,charindex('+',@sql+'+'),N''))
  end
 
  --结果
/*
消息 245，级别 16，状态 1，第 2 行
在将 nvarchar 值 'substring(flbh,1,' 转换成数据类型 int 时失败。
*/

Answer 1

--try
--树型数据之调整编号规则（通用函数+存储过程）
--由于调整编号规则，调整编码的隶属关系和移动复制节点都涉及到调整规则，则写一个通用函数
ALTER function dbo.ChangeCodeRule   -- dbo.ChangeCodeRule('2,2,3','2,4,2','hzfa','flbh','0',0)
  (@old_code_rule nvarchar(20),      --老编号规则
   @new_code_rule nvarchar(20),      --新编号规则
   @Table_Name nvarchar(20),    --编号表表名
   @Field_Name nvarchar(20),    --编号表编号字段名
   @Filled_Char nvarchar(2),       --扩展编号时填充的字符
   @Position int                --扩展和收缩编码时，从那个位置开个收扩
  ) 
  returns nvarchar(4000)
  as
  begin
  --第一步:将新老编号拆分表编码表，以备后用
    --1、创建编码规则表（老）--2,3,4
    declare @old_table_rule table(id int identity(1,1),Code_Len int,Code_Lens int,Code_Char nvarchar(100)) 
    declare @code_len int,
            @code_lens varchar(10),
            @code_char nvarchar(100)
    set @old_code_rule=@old_code_rule+N','
    set @code_lens=1
    while @old_code_rule>''
      begin
        set @code_len=convert(int,left(@old_code_rule,charindex(',',@old_code_rule)-1))
        set @old_code_rule=stuff(@old_code_rule,1,charindex(',',@old_code_rule),N'') --显示转换int至nvarchar
        --set @Code_Char=N'substring('+@Field_Name+N','+@code_lens+N','+@code_len+N')'
set @Code_Char=N'substring('+@Field_Name+N','+rtrim(@code_lens)+N','+@code_len+N')'               insert @old_table_rule(code_len,code_lens,code_char)
                 select @code_len,@code_lens,@code_char
              set @code_lens=@code_lens+@code_len
      end
      --创建编号规则表（新）
    declare @new_table_rule table(id int identity(1,1),code_len int,code_lens int)
    set @new_code_rule=@new_code_rule+','
    set @code_lens=0
    while @new_code_rule>''
      begin
        set @code_len=convert(int,left(@new_code_rule,charindex(',',@new_code_rule)-1))
        set @new_code_rule=stuff(@new_code_rule,1,charindex(',',@new_code_rule),N'')
        set @code_lens=@code_lens+@code_len
              insert @new_table_rule(code_len,code_lens)
                select @code_len,@code_lens
      end
    --重排编号动态T-SQL
    declare @sql nvarchar(4000)
    select @sql=case 
                  when n.code_len=0 then N''                                           --如果新编号长度为0，表示去掉此段代码
                 else+N'
                  case when len('+@field_name+N')<o.code_len then N''''                          
                    else'+                            
                      case when n.code_len=o.code_len then o.code_char                   --若新老编号长度相同，则不需要特殊处理，取原值 
                           when n.code_len>o.code_len then                               --若新编号长度大于老编号长度，则继续判断位置
                             case when @Position=-1 or @position<n.code_len            --此处表示表后面开始填充指定字符
                                  then o.code_char+N'+N'+quotename(replicate(@Filled_Char,n.code_len-o.code_len),N'''')
                               else N'stuff('+o.code_char+N','+cast(@position+1 as int)+N',0'+quotename(replicate(@Filled_Char,n.code_len-o.code_len),N'''')+N')'
                             end   
                      else  --收缩
                             case when @Position=-1 or @position>o.code_len  
                                  then+N'left('+o.code_char+N','+n.code_len-o.code_len+N')'
                               else+ N'stuff('+o.code_char+N','+cast(@position+1 as int)+N','+n.code_len-o.code_len+N','+N''''+N')'
                             end     
                      end
                +N'end'
              end
      from @new_table_rule N,@old_table_rule O
        where n.id=o.id
        
      return(stuff(@sql,1,charindex('+',@sql+'+'),N''))
  end

Answer 2

--错了，这个
--树型数据之调整编号规则（通用函数+存储过程）
--由于调整编号规则，调整编码的隶属关系和移动复制节点都涉及到调整规则，则写一个通用函数
ALTER function dbo.ChangeCodeRule   -- dbo.ChangeCodeRule('2,2,3','2,4,2','hzfa','flbh','0',0)
  (@old_code_rule nvarchar(20),      --老编号规则
   @new_code_rule nvarchar(20),      --新编号规则
   @Table_Name nvarchar(20),    --编号表表名
   @Field_Name nvarchar(20),    --编号表编号字段名
   @Filled_Char nvarchar(2),       --扩展编号时填充的字符
   @Position int                --扩展和收缩编码时，从那个位置开个收扩
  ) 
  returns nvarchar(4000)
  as
  begin
  --第一步:将新老编号拆分表编码表，以备后用
    --1、创建编码规则表（老）--2,3,4
    declare @old_table_rule table(id int identity(1,1),Code_Len int,Code_Lens int,Code_Char nvarchar(100)) 
    declare @code_len int,
            @code_lens varchar(10),
            @code_char nvarchar(100)
    set @old_code_rule=@old_code_rule+N','
    set @code_lens=1
    while @old_code_rule>''
      begin
        set @code_len=convert(int,left(@old_code_rule,charindex(',',@old_code_rule)-1))
        set @old_code_rule=stuff(@old_code_rule,1,charindex(',',@old_code_rule),N'') --显示转换int至nvarchar
        --set @Code_Char=N'substring('+@Field_Name+N','+@code_lens+N','+@code_len+N')'
set @Code_Char=N'substring('+@Field_Name+N','+@code_lens+N','+rtrim(@code_len）+N')'               insert @old_table_rule(code_len,code_lens,code_char)
                 select @code_len,@code_lens,@code_char
              set @code_lens=@code_lens+@code_len
      end
      --创建编号规则表（新）
    declare @new_table_rule table(id int identity(1,1),code_len int,code_lens int)
    set @new_code_rule=@new_code_rule+','
    set @code_lens=0
    while @new_code_rule>''
      begin
        set @code_len=convert(int,left(@new_code_rule,charindex(',',@new_code_rule)-1))
        set @new_code_rule=stuff(@new_code_rule,1,charindex(',',@new_code_rule),N'')
        set @code_lens=@code_lens+@code_len
              insert @new_table_rule(code_len,code_lens)
                select @code_len,@code_lens
      end
    --重排编号动态T-SQL
    declare @sql nvarchar(4000)
    select @sql=case 
                  when n.code_len=0 then N''                                           --如果新编号长度为0，表示去掉此段代码
                 else+N'
                  case when len('+@field_name+N')<o.code_len then N''''                          
                    else'+                            
                      case when n.code_len=o.code_len then o.code_char                   --若新老编号长度相同，则不需要特殊处理，取原值 
                           when n.code_len>o.code_len then                               --若新编号长度大于老编号长度，则继续判断位置
                             case when @Position=-1 or @position<n.code_len            --此处表示表后面开始填充指定字符
                                  then o.code_char+N'+N'+quotename(replicate(@Filled_Char,n.code_len-o.code_len),N'''')
                               else N'stuff('+o.code_char+N','+cast(@position+1 as int)+N',0'+quotename(replicate(@Filled_Char,n.code_len-o.code_len),N'''')+N')'
                             end   
                      else  --收缩
                             case when @Position=-1 or @position>o.code_len  
                                  then+N'left('+o.code_char+N','+n.code_len-o.code_len+N')'
                               else+ N'stuff('+o.code_char+N','+cast(@position+1 as int)+N','+n.code_len-o.code_len+N','+N''''+N')'
                             end     
                      end
                +N'end'
              end
      from @new_table_rule N,@old_table_rule O
        where n.id=o.id
        
      return(stuff(@sql,1,charindex('+',@sql+'+'),N''))
  end
 
  

Answer 3

/*
消息 245，级别 16，状态 1，第 2 行
在将 nvarchar 值 'substring(flbh,1,' 转换成数据类型 int 时失败。
*/

Answer 4

--结果
/*
消息 245，级别 16，状态 1，第 2 行
在将 nvarchar 值 'substring(flbh,1,' 转换成数据类型 int 时失败。
*/
查看flbh中的数据是否都能转换为int型数据.

Answer 5

set @Code_Char=N'substring('+@Field_Name+N','+@code_lens+N','+ltrim(@code_len)+N')'

Answer 6

  begin
        set @code_len=convert(int,left(@old_code_rule,charindex(',',@old_code_rule)-1))
        set @old_code_rule=stuff(@old_code_rule,1,charindex(',',@old_code_rule),N'')
        set @Code_Char=N'substring('+@Field_Name+N','+@code_lens+N','+@code_len+N')' set @Code_Char=N'substring('+@Field_Name+N','+@code_lens+N','+cast(@code_len as nvarchar(5)）+N')'

Answer 7

set @Code_Char=N'substring('+@Field_Name+N','+@code_lens+N','+cast(@code_len as nvarchar(5)）+N')' 

Answer 8

(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)
消息 245，级别 16，状态 1，第 46 行
在将 nvarchar 值 'substring(flbh,1,2)' 转换成数据类型 int 时失败。

Answer 9

消息 245，级别 16，状态 1，第 1 行
在将 nvarchar 值 '
                  case when len(flbh)<o.code_len then N''                          
                    else' 转换成数据类型 int 时失败。

Answer 10

试试
--树型数据之调整编号规则（通用函数+存储过程）
--由于调整编号规则，调整编码的隶属关系和移动复制节点都涉及到调整规则，则写一个通用函数
ALTER function [dbo].[ChangeCodeRule]  
  (@old_code_rule nvarchar(20),      --老编号规则
   @new_code_rule nvarchar(20),      --新编号规则
   @Table_Name nvarchar(20),    --编号表表名
   @Field_Name nvarchar(20),    --编号表编号字段名
   @Filled_Char nvarchar(2),       --扩展编号时填充的字符
   @Position int                --扩展和收缩编码时，从那个位置开个收扩
  ) 
  returns nvarchar(4000)
  as
  begin
  --第一步:将新老编号拆分表编码表，以备后用
    --1、创建编码规则表（老）--2,3,4
    declare @old_table_rule table(id int identity(1,1),Code_Len int,Code_Lens int,Code_Char nvarchar(100)) 
    declare @code_len int,
            @code_lens varchar(10),
            @code_char nvarchar(100)
    set @old_code_rule=@old_code_rule+N','
    set @code_lens=1
    while @old_code_rule>''
      begin
        set @code_len=convert(int,left(@old_code_rule,charindex(',',@old_code_rule)-1))
        set @old_code_rule=stuff(@old_code_rule,1,charindex(',',@old_code_rule),N'')        --显示转换int至nvarchar
        --set @Code_Char=N'substring('+@Field_Name+N','+@code_lens+N','+@code_len+N')'
        set @Code_Char=N'substring('+@Field_Name+N','+cast(@code_lens as varchar)+N','+cast(@code_len as varchar)+N')'               insert @old_table_rule(code_len,code_lens,code_char)
                 select @code_len,@code_lens,@code_char
              set @code_lens=@code_lens+@code_len
      end
      --创建编号规则表（新）
    declare @new_table_rule table(id int identity(1,1),code_len int,code_lens int)
    set @new_code_rule=@new_code_rule+','
    set @code_lens=0
    while @new_code_rule>''
      begin
        set @code_len=convert(int,left(@new_code_rule,charindex(',',@new_code_rule)-1))
        set @new_code_rule=stuff(@new_code_rule,1,charindex(',',@new_code_rule),N'')
        set @code_lens=@code_lens+@code_len
              insert @new_table_rule(code_len,code_lens)
                select @code_len,@code_lens
      end
    --重排编号动态T-SQL
    declare @sql nvarchar(4000)
    select @sql=case 
                  when n.code_len=0 then N''''                                           --如果新编号长度为0，表示去掉此段代码
                 else+N'
                  +case when len('+@field_name+N')<'+cast(o.code_len as varchar)+N' then N''''                          
                    else '+                            
                      case when n.code_len=o.code_len then o.code_char                   --若新老编号长度相同，则不需要特殊处理，取原值 
                           when n.code_len>o.code_len then                               --若新编号长度大于老编号长度，则继续判断位置
                             case when @Position=-1 or @position<n.code_len            --此处表示表后面开始填充指定字符
                                  then o.code_char+N'+N'+quotename(replicate(@Filled_Char,n.code_len-o.code_len),N'''')
                               else N'stuff('+o.code_char+N','+cast(@position+1 as varchar)+N',0'+quotename(replicate(@Filled_Char,n.code_len-o.code_len),N'''''')+N','+N''''''+N')'
                             end   
                      else  --收缩
                             case when @Position=-1 or @position>o.code_len  
                                  then+N'left('+o.code_char+N','+cast(n.code_len-o.code_len as varchar(10))+N')'
                               else+ N'stuff('+o.code_char+N','+cast(@position+1 as varchar)+N','+cast(n.code_len-o.code_len as varchar(10))+','+N''''''+N')'
                             end     
                      end
                +N' end'
              end
      from @new_table_rule N,@old_table_rule O
        where n.id=o.id
      return(@sql)
  end
 --       select  dbo.ChangeCodeRule('2,2,3','6,4,2','hzfa','flbh','0',3)
--                     +case when len(flbh)<3 then N''                                                else stuff(substring(flbh,5,3),4,-1,'') end

Answer 11

刚才发的不完全，
我也是刚学的
注意：变量一般都要初始化，否则值无法确定，值于为什么要将O.CODELEN之类的转为VARCHAR，我想是因为定义表时的原因，具体的，就只有等真的高手来解决了。
--树型数据之调整编号规则（通用函数+存储过程）
--由于调整编号规则，调整编码的隶属关系和移动复制节点都涉及到调整规则，则写一个通用函数
ALTER function [dbo].[ChangeCodeRule]  
  (@old_code_rule nvarchar(20),      --老编号规则
   @new_code_rule nvarchar(20),      --新编号规则
   @Table_Name nvarchar(20),    --编号表表名
   @Field_Name nvarchar(20),    --编号表编号字段名
   @Filled_Char nvarchar(2),       --扩展编号时填充的字符
   @Position int                --扩展和收缩编码时，从那个位置开个收扩
  ) 
  returns nvarchar(4000)
  as
  begin
  --第一步:将新老编号拆分表编码表，以备后用
    --1、创建编码规则表（老）--2,3,4
    declare @old_table_rule table(id int identity(1,1),Code_Len int,Code_Lens int,Code_Char nvarchar(100)) 
    declare @code_len int,
            @code_lens varchar(10),
            @code_char nvarchar(100)
    set @old_code_rule=@old_code_rule+N','
    set @code_lens=1
    while @old_code_rule>''
      begin
        set @code_len=convert(int,left(@old_code_rule,charindex(',',@old_code_rule)-1))
        set @old_code_rule=stuff(@old_code_rule,1,charindex(',',@old_code_rule),N'')        --显示转换int至nvarchar
        --set @Code_Char=N'substring('+@Field_Name+N','+@code_lens+N','+@code_len+N')'
        set @Code_Char=N'substring('+@Field_Name+N','+cast(@code_lens as varchar)+N','+cast(@code_len as varchar)+N')'               insert @old_table_rule(code_len,code_lens,code_char)
                 select @code_len,@code_lens,@code_char
              set @code_lens=@code_lens+@code_len
      end
      --创建编号规则表（新）
    declare @new_table_rule table(id int identity(1,1),code_len int,code_lens int)
    set @new_code_rule=@new_code_rule+','
    set @code_lens=0
    while @new_code_rule>''
      begin
        set @code_len=convert(int,left(@new_code_rule,charindex(',',@new_code_rule)-1))
        set @new_code_rule=stuff(@new_code_rule,1,charindex(',',@new_code_rule),N'')
        set @code_lens=@code_lens+@code_len
              insert @new_table_rule(code_len,code_lens)
                select @code_len,@code_lens
      end
    --重排编号动态T-SQL
    declare @sql nvarchar(4000)
    set @sql=''
    select @sql=@sql+case 
                  when n.code_len=0 then N''''                                           --如果新编号长度为0，表示去掉此段代码
                 else N'
                  +case when len('+@field_name+N')<'+cast(o.code_len as varchar)+N' then N''''                          
                    else '+                            
                      case when n.code_len=o.code_len then o.code_char                   --若新老编号长度相同，则不需要特殊处理，取原值 
                           when n.code_len>o.code_len then                               --若新编号长度大于老编号长度，则继续判断位置
                             case when @Position=-1 or @position<n.code_len            --此处表示表后面开始填充指定字符
                                  then o.code_char+N'+N'+quotename(replicate(@Filled_Char,n.code_len-o.code_len),N'''')
                               else N'stuff('+o.code_char+N','+cast(@position+1 as varchar)+N',0'+quotename(replicate(@Filled_Char,n.code_len-o.code_len),N'''''')+N','+N''''''+N')'
                             end   
                      else  --收缩
                             case when @Position=-1 or @position>o.code_len  
                                  then+N'left('+o.code_char+N','+cast(n.code_len-o.code_len as varchar(10))+N')'
                               else+ N'stuff('+o.code_char+N','+cast(@position+1 as varchar)+N','+cast(n.code_len-o.code_len as varchar(10))+','+N''''''+N')'
                             end     
                      end
                +N' end'
              end
      from @new_table_rule N,@old_table_rule O
        where n.id=o.id
      return(stuff(@sql,1,charindex('+',@sql),N''))
  end
 --       select  dbo.ChangeCodeRule('1,2,3','3,4,2','hzfa','flbh','0',3)
 
 /*最后生成的编码重排语句
 case when len(flbh)<1 then N''                                  
               else stuff(substring(flbh,1,1),4,0'00','') end            
                       +case when len(flbh)<2 then N''                                       
                                else substring(flbh,2,2)+N'00' end                 
                                   +case when len(flbh)<3 then N''                                          
       else stuff(substring(flbh,4,3),4,-1,'') end
*/