ÓÃSQLÉú³ÉÁ÷Ë®ºÅ

ÓÃSQLÉú³ÉÁ÷Ë®ºÅ
ת£ºÎ¤½­ÌΠ   ·¢±íÓÚ2010Äê02ÔÂ03ÈÕ 09:38 ÔĶÁ(4) ÆÀÂÛ(0)
 
 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_FillNumberWithZero]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_FillNumberWithZero]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_FormatDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_FormatDate]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_GetNewFlowNumber]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_GetNewFlowNumber]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_GetNowDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_GetNowDate]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*
Éú³ÉÁ÷Ë®ºÅºóÃ漸λÊý×Ö×Ö·ûµÄÏà¹Øº¯Êý
²»×ãλÊýÔÚ×ó±ßÓÃ0Ìî³ä
*/
CREATE FUNCTION dbo.fn_FillNumberWithZero
(
--Ìî³äµÄÊý×Ö
@num int,
--×ÜλÊý
@len int
)
RETURNS varchar(50) AS
BEGIN
--Èç¹û´«ÈëµÄÁ÷Ë®ºÅ´óÓÚ×ܵij¤¶È£¬ÄÇôֱ½Ó·µ»ØÁ÷Ë®ºÅ×Ö·û´®¸ñʽ
if(len(Convert(varchar(50),@num))>@len)
   return Convert(varchar(50),@num)
ELSE
   BEGIN
    --ÐèÒªÌî³ä0µÄλÊý
    declare @NeedFillLen int
    set @NeedFillLen=@Len-len(Convert(varchar(50),@num))
    --»ñÈ¡ÐèÒªÌî³äµÄ0µÄ×Ö·û´®
    declare @i int
    set @i=0
    declare @temp varchar(50)
    set @temp=N''
    while @i<@NeedFillLen
     BEGIN
      SET @temp=@temp+'0'
      SET @i=@i+1
     END
    --·µ»Ø×éºóµÄ×Ö·û´®
    return @temp+Convert(varchar(50),@num)
   END
return ''
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO