关于 T-SQL 的几点小九九

核心提示： 4. 如何利用 T-SQL 编程满足一个苛刻的需求有一天，老板对你说：我需要每条记录的编号是按照指定的格式生成的，关于 T-SQL 的几点小九九(7)，并且具有唯一性，而不只是单纯连续的 1，*/IF@curDay=1AND@Count=0--每个月1号之后，在没有插入任何新数据之前，2，

4. 如何利用 T-SQL 编程满足一个苛刻的需求

有一天，老板对你说：我需要每条记录的编号是按照指定的格式生成的，并且具有唯一性，而不只是单纯连续的 1，2，3，4……这些数字，明白了吗？

您还记得上面那张表吗？对，里面有一个 PlanID 列，这个字段是用来描述采购计划编号的，老板告诉我，最理想的情况就是按照 DP-P-YYMMXXXXX 的格式生成，具体点就是：例如现在是 6 月份如果填写这张表的记录，理应生成 DP-P-090600001，DP-P-090600002，DP-P-090600003……这样的记录，并且需要满足多人操作，也就是能够满足良好的并发操作。

好了，来看看我的初步解决方案是如何满足这样的需求的：

------------------------　同步机制生成序列值的存储过程　------------------------
USE　D_Dowpol;
GO
IF　OBJECT_ID('[dbo].[SyncSeq]')　IS　NOT　NULL
DROP　TABLE　[dbo].[SyncSeq];
GO
CREATE　TABLE　[dbo].[SyncSeq]　(val　INT);
INSERT　INTO　[dbo].[SyncSeq]　VALUES(0);

IF　OBJECT_ID('[dbo].[usp_SyncSeq]')　IS　NOT　NULL
　　　DROP　PROC　[dbo].[usp_SyncSeq];
GO
CREATE　PROC　[dbo].[usp_SyncSeq]
(
　　　@val　AS　INT　OUTPUT
)
AS
　　　UPDATE　[dbo].[SyncSeq]
　　　SET　@val　=　val　=　val　+　1;　　　　　　--　T-SQL　专有语法，等价于　SET　val　=　val　+　1,　@val　=　val　+　1

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　--　SQL　Server　会将先排它地锁定该行并递增val，获取该值，事务完成后释放该锁
GO

USE　D_Dowpol
IF　EXISTS　(SELECT　[name]　FROM　sysobjects
WHERE　[name]　=　'proc_AddProcurementPlanData'　AND　type　=　'P')
DROP　PROCEDURE　proc_AddProcurementPlanData
GO
CREATE　PROCEDURE　[dbo].[proc_AddProcurementPlanData]
(
　　@CreateDate　　datetime,
　　@ProcurementItem　nvarchar(500),
　　@SupplierID　　int,
　　@PlannedType　bit,
　　@PlannedFund　decimal(14,2),
　　@Dollar　　decimal(14,2),
　　@Remark　　nvarchar(2000),
　　@EnteringEmp　nvarchar(50),
　　@Assessor　　nvarchar(50)
)
AS
BEGIN
　　　　　　SET　NOCOUNT　ON

　　　　　　DECLARE　@curDay　AS　DATETIME,
　　　　　　　　　　　　　@upd_rowcount　int,
　　　　　　　　　　　　　@ins_error　int,
　　　　　　　　　　　　　　@IDENTITY　AS　INT,
　　　　　　　　　　　　　@Key　AS　INT,
　　　　　　　　　　　　　@ID　nvarchar(50),
　　　　　　　　　　　　　@Count　AS　INT,　　　　　　　　　　　　　　　　　　　　----　用于统计每月1号插入的数据的条数
　　　　　　　　　　　　　　@PlanID　nvarchar(100)
　　　　　　--　防止“备注”中只输入一堆空格字符进入数据库。
　　　　　　SELECT　@Remark　=　REPLACE(@Remark,　'　',　''),
　　　　　　　　　　　　　@PlanID　=　'DP_P_'　+　CONVERT(nvarchar(4),　GETDATE(),　12),
　　　　　　　　　　@curDay　=　DAY(GETDATE())

　　　　　　BEGIN　TRAN　AddPlanTran　----　事务开始　----

　　　　　　SELECT　@Count　=　(SELECT　COUNT(AutoID)
　　　　　　　　　　　　　　　　　　　　　　　　　　FROM　D_Dowpol.dbo.ProcurementPlan
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　WHERE　PlanID　LIKE　@PlanID　+　'%')

　　　　　　IF　@Remark　IS　NULL　OR　(DATALENGTH(@Remark)　=　0)
　　　　　　　BEGIN
　　　　　　　　　INSERT　INTO　D_Dowpol.dbo.ProcurementPlan
　　　　　　　　　　　　(CreateDate,　ProcurementItem,　SupplierID,　PlannedType,　PlannedFund,　Dollar,　EnteringEmp,　Assessor)
　　　　　　　　VALUES
　　　　　　　　　　　　(@CreateDate,　@ProcurementItem,　@SupplierID,　@PlannedType,　@PlannedFund,　@Dollar,　@EnteringEmp,　@Assessor)
　　　　　　　END
　　　　　　　ELSE
　　　　　　　BEGIN
　　　　　　　　　INSERT　INTO　D_Dowpol.dbo.ProcurementPlan
　　　　　　　　　　　　(CreateDate,　ProcurementItem,　SupplierID,　PlannedType,　PlannedFund,　Dollar,　Remark,　EnteringEmp,　Assessor)
　　　　　　　VALUES
　　　　　　　　　　　　(@CreateDate,　@ProcurementItem,　@SupplierID,　@PlannedType,　@PlannedFund,　@Dollar,　@Remark,　@EnteringEmp,　@Assessor)
　　　　　　　END

　　　　　　　SELECT　@ins_error　=　@@ERROR

　　　　　　　IF　@ins_error　=　0
　　　　　　　BEGIN
　　　　　　

　　　　　　　　　SELECT　@IDENTITY　=　IDENT_CURRENT('ProcurementPlan')
　　　　　　　　　　　　/*
　　　　　　　　　　　　　还存在一个缺陷，就是假设服务器上正确的时间是8月份，忽然因为出现一些异常，有人把服务器上的
　　　　　　　　　　　　　时间改成了7月，而且七月的　DP-P-09070018，然后实际上序列生成器中的序列值在8月份没有出现异
　　　　　　　　　　　　　常的时候已经生存到9，显然如果这个时候有人要插入数据进入数据库，会出现错误，因为此刻生成的
　　　　　　　　　　PlanID为：DP-P-09070009，而这个编号已经在7月份有人插入数据时生成了，所以对设置了唯一约束
　　　　　　　　　　的PlanID字段来说，就会报错了。如何解决这个问题呢？
　　　　　　　　　　　　　我的初步想法就是，每次进来之前都根据　@Count的值与　D_Dowpol.dbo.SyncSeq　表中的值做判断，如果
　　　　　　　　　　　　　不相符的话，那就首先将　D_Dowpol.dbo.SyncSeq　的值修改为　@Count　后再执行其他逻辑即可。
　　　　　　　　　　　　*/
　　　　　　　　　　　IF　@curDay　=　1　AND　@Count　=　0　--　每个月1号之后，在没有插入任何新数据之前，把序列生成器首先归零，每个月仅此一次
　　　　　　　　　　　BEGIN
　　　　　　　　　　　　　　UPDATE　D_Dowpol.dbo.SyncSeq
　　　　　　　　　　　　　　SET　val　=　0
　　　　　　　　　　　END

　　　　　　　　　　　EXEC　D_Dowpol.dbo.usp_SyncSeq　@Key　OUTPUT　--　获取同步序列生成器生成的序列值

　　　　　　　　　　　　IF　@Key　BETWEEN　0　AND　9
　　　　　　　　　　　　　　SELECT　@ID　=　'000'　+　CAST(@Key　AS　nvarchar(10))
　　　　　　　　　　　　ELSE　IF　@Key　BETWEEN　10　AND　99
　　　　　　　　　　　　　SELECT　@ID　=　'00'　+　CAST(@Key　AS　nvarchar(10))
　　　　　　　　　　　　ELSE　IF　@Key　BETWEEN　100　AND　999
　　　　　　　　　　　　　SELECT　@ID　=　'0'　+　CAST(@Key　AS　nvarchar(10))
　　　　　　　　　　　　ELSE
　　　　　　　　　　　　　SELECT　@ID　=　CAST(@Key　AS　nvarchar(10))

　　　　　　　　　　　　UPDATE　D_Dowpol.dbo.ProcurementPlan
　　　　　　　　　　　　　　　SET　PlanID　=　@PlanID　+　@ID
　　　　　　　　　　　　　　　　　　WHERE　AutoID　=　@IDENTITY

　　　　　　　　　　　　SELECT　@upd_rowcount　=　@@ROWCOUNT

　　　　　　　　　　　　IF　@upd_rowcount　>　0
　　　　　　　　　　　　BEGIN
　　　　　　　　　　　　　　　　SELECT　1　AS　Result　　--　返回1表示唯一的成功标识？
　　　　　　　　　　　　　　　　COMMIT　TRAN　AddPlanTran
　　　　　　　　　　　　END
　　　　　　　　　　　　ELSE
　　　　　　　　　　　　BEGIN
　　　　　　　　　　　　　　　　SELECT　2　AS　Result　　--　返回2表示在修改　D_Dowpol.dbo.ProcurementPlan　表的　PlanID　字段时出现错误并且回滚
　　　　　　　　　　　　　　　　ROLLBACK　TRAN　AddPlanTran
　　　　　　　　　　　　END
　　　　　　　END
　　　　　　　ELSE
　　　　　　　BEGIN
　　　　　　　　　　　SELECT　3　AS　Result　　　　　　--　返回3表示在向　D_Dowpol.dbo.ProcurementPlan　表插入数据时就出现了错误并且回滚
　　　　　　　　　　　ROLLBACK　TRAN　AddPlanTran
　　　　　　　END
END
GO