USE [Erp]
GO
/****** Object: StoredProcedure [dbo].[PROC_jit_seq_SeqNo] Script Date: 09/20/2012 14:04:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[PROC_jit_seq_SeqNo] @Date datetime
AS
BEGIN
DECLARE @SeqNo VARCHAR(8)
SET @SeqNo=''
SELECT @SeqNo=Seq FROM jit_seq
WHERE dateadd(dd,datediff(dd,0,SeqDate),0) = dateadd(dd,datediff(dd,0,@Date),0)
SET @SeqNo=ISNULL(@SeqNo,'')
IF @SeqNo=''
BEGIN
SET @SeqNo= '0001'
INSERT INTO jit_seq values(dateadd(dd,datediff(dd,0,getdate()),0),@SeqNo)
END
ELSE
BEGIN
SET @SeqNo = RIGHT( '0000' + CAST( @SeqNo + 1 AS VARCHAR(8) ) , 4)
UPDATE jit_seq SET Seq=@SeqNo WHERE dateadd(dd,datediff(dd,0,SeqDate),0) = dateadd(dd,datediff(dd,0,@Date),0)
END
select @SeqNo as SeqNo
END我想了解一下 这段存储过程是什么意思? 而且里面每一步都代表什么? 还有dateadd,datediff函数是什么意思?
--> 业务逻辑应该去问开发人员.而且里面每一步都代表什么?
--> 代表一个步骤.还有dateadd,datediff函数是什么意思?
--> MSSQL的日期函数,
dateadd 参考http://msdn.microsoft.com/zh-cn/library/aa258267(v=sql.80).aspx
datediff 参考http://msdn.microsoft.com/en-us/library/aa258269(v=sql.80).aspx
GO--完整上面操作
/****** Object: StoredProcedure [dbo].[PROC_jit_seq_SeqNo] Script Date: 09/20/2012 14:04:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--上面4行可以不管
ALTER PROC [dbo].[PROC_jit_seq_SeqNo] --存储过程名,create 为创建,因为需要重复执行,所以这里用alter
@Date datetime --该存储过程需要一个参数,@date,类型为datetime,必传
AS
BEGIN
DECLARE @SeqNo VARCHAR(8)--定义一个字符串
SET @SeqNo='' --初始化字符串
SELECT @SeqNo=Seq FROM jit_seq --从jit_seq表中,获取 存储过程传入的@date参数与1900-01-01这个日期的天数差,seq值,并付给@seqno,
WHERE dateadd(dd,datediff(dd,0,SeqDate),0) = dateadd(dd,datediff(dd,0,@Date),0)
SET @SeqNo=ISNULL(@SeqNo,'')--如果上一部没有符合的值,则取空字符串给该变量
IF @SeqNo='' --如果变量为空字符串,赋值0001,然后插入jit_seq表
BEGIN
SET @SeqNo= '0001'
INSERT INTO jit_seq values(dateadd(dd,datediff(dd,0,getdate()),0),@SeqNo)
END
ELSE --如果变量不是空字符串,则做拼接运算。这部分你可以单独拿出来执行一下看看出来什么结果
BEGIN
SET @SeqNo = RIGHT( '0000' + CAST( @SeqNo + 1 AS VARCHAR(8) ) , 4)
UPDATE jit_seq SET Seq=@SeqNo WHERE dateadd(dd,datediff(dd,0,SeqDate),0) = dateadd(dd,datediff(dd,0,@Date),0)
END
select @SeqNo as SeqNo
END
datediff