|
สร้าง Auto ID หรือลำดับที่มีตัวอักษร ปี, เดือน บน SQL Server ด้วย Stored Procedure |
สร้าง Auto ID หรือลำดับที่มีตัวอักษร ปี, เดือน บน SQL Server ด้วย Stored Procedure วิธีการสร้าง Auto ID หรือ Auto Number ถ้าใช้ Database ของ SQL Server การเขียนบน Stored Procedure เป็นอีกวิธีหนึ่งที่จะช่วยให้การสร้าง Auto ID นั้นง่ายและสะดวกมาก เพราะสามารถที่จะเขียนเงื่อนไขต่าง ๆ เช้น การ Select ค่า ID หรือการ Update ID ที่เป็นลำดับบน Stored Procedure ได้เลย ซึ่งตัว Result ที่ได้ เราจะใช้การส่งค่ากลับในรูแบบของ OUT หรือ OUTPUT จากนั้นช้ฝั่งของโปรแกรมเช่น PHP, .Net หรือ Java อ่านค่า ID นั้นไปใช้งานได้เลย
Auto ID Number ด้วย SQL Server (Stored Procedure)
ในตัวอย่างนี้จะประกอบด้วย 2 ตัวอย่างคือ
1. สร้าง ID แบบทีตัวอักษรและลำดับ เช่น TC-00001
2. สร้าง ID แบบที่มี ปี, เดือน และลำดับ เช่น TC-2016-12-0001
สำหรับ Stored Procedure ในตัวอย่างนี้จะใช้การส่งค่ากลับแบบ OUTPUT ซึ่งจะมีตัวอย่างการเชื่อมต่อกับ Stored Procedure ของ SQL Server ในรูปแบบต่าง ๆ ในส่วนท้ายของบทความ
Ex1. สร้าง Auto ID แบบทีตัวอักษรและลำดับ เช่น TC-00001
ใน Concept ของตัวอย่างแรก ให้สร้าง Table ง่าย ๆ สำหรับจัดเก็บลำดับแบบง่าย ๆ โดยจัดเก็บเป็นตัวเลข ที่เป็น ID ล่าสุด เพื่อจะได้นำไป +1 เมื่อมีการดึงค่า ID ใหม่
Table : GenerateNumber
CREATE TABLE [dbo].[GenerateNumber](
[Sequence] [int] NOT NULL,
CONSTRAINT [PK_GenerateNumber] PRIMARY KEY CLUSTERED
(
[Sequence] ASC
)) ON [PRIMARY]
GO
INSERT INTO GenerateNumber (Sequence) VALUES ('1');
![SQL Server Stored Procedure Generate Auto ID Auto Number SQL Server Stored Procedure Generate Auto ID Auto Number](https://www.thaicreate.com/upload/stock/20161228145055.jpg?v=1001)
Stored Procedure : GetAutoIDNo
CREATE PROCEDURE [dbo].[GetAutoIDNo]
@sPrefix VARCHAR(10),
@iLength INT,
@sAutoID VARCHAR(14) OUTPUT
AS
SET DATEFORMAT DMY;
BEGIN
SET NOCOUNT ON;
DECLARE @iSeq INT = 0;
BEGIN TRY
SELECT @iSeq = ISNULL(Sequence,0) FROM GenerateNumber WITH (NOLOCK) ;
SET @sAutoID = @sPrefix + '-' + CAST(REPLACE(STR(@iSeq, @iLength), SPACE(1), '0') AS VARCHAR(20))
SET @iSeq = @iSeq + 1;
UPDATE GenerateNumber SET [Sequence] = @iSeq;
END TRY
BEGIN CATCH
SET @sAutoID = @sPrefix + '-00000';
END CATCH
RETURN 0;
END
Result
DECLARE @return_value int,
@sAutoID varchar(14)
EXEC @return_value = [dbo].[GetAutoIDNo]
@sPrefix = N'TC',
@iLength = 5,
@sAutoID = @sAutoID OUTPUT
SELECT @sAutoID as N'@sAutoID'
![SQL Server Stored Procedure Generate Auto ID Auto Number SQL Server Stored Procedure Generate Auto ID Auto Number](https://www.thaicreate.com/upload/stock/20161228145118.jpg?v=1001)
ID ที่ได้
![](/images/adv.jpg)
Ex2. สร้าง Auto ID แบบทีตัวอักษร ปี, เดือน และลำดับ เช่น TC-2016-12-00001
ใน Concept ของตัวอย่างที่ 2 ให้สร้าง Table ง่าย ๆ สำหรับจัดเก็บลำดับแบบง่าย ๆ โดย Table ให้มีการแยกจัดเก็บ ปี และ เดือน และ ลำดับ โดยรายการ เดือนและปี รวมทั้งลำดับ จะมีการ Insert ลงอัตโนมัติ เมื่อขึ้น ปี หรือ เดือน ใหม่
Table : GenerateNumber
CREATE TABLE [dbo].[GenerateNumber](
[Year] [int] NOT NULL,
[Month] [int] NOT NULL,
[Sequence] [int] NOT NULL,
CONSTRAINT [PK_GenerateNumber] PRIMARY KEY CLUSTERED
(
[Year] ASC,
[Month] ASC,
[Sequence] ASC
)) ON [PRIMARY]
![SQL Server Stored Procedure Generate Auto ID Auto Number SQL Server Stored Procedure Generate Auto ID Auto Number](https://www.thaicreate.com/upload/stock/20161228145330.jpg?v=1001)
Stored Procedure : GetAutoIDNo
CREATE PROCEDURE [dbo].[GetAutoIDNo]
@sPrefix VARCHAR(10),
@iLength INT,
@sAutoID VARCHAR(30) OUTPUT
AS
SET DATEFORMAT DMY;
BEGIN
SET NOCOUNT ON;
DECLARE @iYear INT = YEAR(GETDATE());
DECLARE @iMonth INT = MONTH(GETDATE());
DECLARE @iSeq INT = 0;
BEGIN TRY
SELECT @iSeq = ISNULL([Sequence],0) FROM GenerateNumber WITH (NOLOCK) WHERE [Year] = @iYear AND [Month] = @iMonth;
IF @iSeq = 0
BEGIN
SET @iSeq = 1;
INSERT INTO GenerateNumber ([Year],[Month],[Sequence]) VALUES (@iYear, @iMonth, @iSeq);
END
SET @sAutoID = @sPrefix + '-'
+ CAST(REPLACE(STR(@iYear, 4), SPACE(1), '0') AS VARCHAR(4)) + '-'
+ CAST(REPLACE(STR(@iMonth, 2), SPACE(1), '0') AS VARCHAR(2)) + '-'
+ CAST(REPLACE(STR(@iSeq, @iLength), SPACE(1), '0') AS VARCHAR(20))
SET @iSeq = @iSeq + 1;
UPDATE GenerateNumber SET [Sequence] = @iSeq WHERE [Year] = @iYear AND [Month] = @iMonth;
END TRY
BEGIN CATCH
SET @sAutoID = @sPrefix + '-0000-00-00000';
END CATCH
RETURN 0;
END
Result
DECLARE @return_value int,
@sAutoID varchar(30)
EXEC @return_value = [dbo].[GetAutoIDNo]
@sPrefix = N'TC',
@iLength = 5,
@sAutoID = @sAutoID OUTPUT
SELECT @sAutoID as N'@sAutoID'
![SQL Server Stored Procedure Generate Auto ID Auto Number SQL Server Stored Procedure Generate Auto ID Auto Number](https://www.thaicreate.com/upload/stock/20161228145454.jpg?v=1001)
ID ที่มี ปี และ เดือน มาเกี่ยวข้อง
![SQL Server Stored Procedure Generate Auto ID Auto Number SQL Server Stored Procedure Generate Auto ID Auto Number](https://www.thaicreate.com/upload/stock/20161228145459.jpg?v=1001)
ID ที่มี ปี และ เดือน มาเกี่ยวข้อง
![SQL Server Stored Procedure Generate Auto ID Auto Number SQL Server Stored Procedure Generate Auto ID Auto Number](https://www.thaicreate.com/upload/stock/20161228145505.jpg?v=1001)
ในกรณีที่ขึ้น เดือน หรือ ปี ใหม่ ก็จะเริ่มนับ 1 ใหม่
![SQL Server Stored Procedure Generate Auto ID Auto Number SQL Server Stored Procedure Generate Auto ID Auto Number](https://www.thaicreate.com/upload/stock/20161228145509.jpg?v=1001)
รายการ Table ที่เกี่ยวข้อง ที่ถูก Insert อัตโนมัติ
Note!! ในตัวอย่างนี้จะส่งค่ากลับจาก Stored Procedure ในรูปแบบของ OUTPUT ฉะนั้นการอ่านค่าด้วยภาษาต่าง ๆ จะแตกต่างกันไป
PHP กับ SQL Server Stored Procedure
Visual Basic (VB.Net) เรียกใช้ EXEC SQL Server Stored Procedure
Visual C# (C# .Net) เรียกใช้ EXEC/CALL - SQL Server Stored Procedure
Java เรียกใช้ EXEC/CALL - SQL Server Stored Procedure ด้วย (JDBC)
|
|
|
By : |
TC Admin
|
|
Article : |
บทความเป็นการเขียนโดยสมาชิก หากมีปัญหาเรื่องลิขสิทธิ์ กรุณาแจ้งให้ทาง webmaster ทราบด้วยครับ |
|
Score Rating : |
![](/images/resource/startrue.gif) ![](/images/resource/startrue.gif) ![](/images/resource/startrue.gif) |
|
Create Date : |
2016-12-28 |
|
Download : |
No files |
|
Sponsored Links |
|
|
|
|