27,579
社区成员
发帖
与我相关
我的任务
分享
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Rocky
-- Create date: 2013-09-09
-- Description: 获取请假单编号,编码原则:QJ+年月日(6位)+流水号(4位)
-- =============================================
ALTER FUNCTION [dbo].[ufn_GetLeaveNo]()
RETURNS VARCHAR(20)
AS
BEGIN
-- Declare the return variable here
DECLARE @Form_No VARCHAR(20)
DECLARE @Temp VARCHAR(20)
SET @Temp=''
SET @Form_No='QJ'+CONVERT(VARCHAR(6),GETDATE(),12)
-- Add the T-SQL statements to compute the return value here
SELECT @Temp=MAX(Form_No)
FROM dbo.Att_Leave t (NOLOCK)
WHERE Form_No LIKE @Form_No+'%'
IF @Temp IS NULL
BEGIN
SET @Form_No=@Form_No+'00001'
END
ELSE
BEGIN
SET @Temp=RIGHT(100000+RIGHT(@Temp,5)+1,5)
SET @Form_No=@Form_No+@Temp
END
-- Return the result of the function
RETURN @Form_No
END