新增文章

文章标题分类内容

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--【函数】【删除】

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.aBillNo') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
               DROP     FUNCTION                                       dbo.aBillNo
   GO
   
   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--【函数】dbo.aBillNo( 'No.','0',4,123 ) 【生成编号】

CREATE FUNCTION dbo.aBillNo
   (
       @Start    varchar(20)  -- 前缀
      ,@Replace  varchar(10)  -- 补齐文字
      
      ,@Len  int  -- 长度
      ,@Num  int  -- 数字
   )
   RETURNS varchar(max) 
   AS 
   BEGIN
   -----------------------------------------------------------------------
      declare @txt varchar(50) = cast(@num as varchar);

if len(@num) > @len begin
         set @len = 0
      end
      else begin 
         set @len = @len - len(@num) 
      end;

return  @Start + replicate( @Replace,@len ) + @txt
      
   END
   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* 调用：

SELECT dbo.aBillNo( 'No.','x',4 ,12     )  -- No.xx12
   
   SELECT dbo.aBillNo( 'No.','0',4 ,12     )  -- No.0012
   SELECT dbo.aBillNo( 'No.','0',4 ,123456 )  -- No.123456
*/