การเขียน Store Procedure บน SQLSERVER

เกริ่นนำ
บทความนี้ผมจะพยายามอธิบายการเขียน Store Procedure ซึ่งนับได้ว่าเป็นพื้นฐานสำหรับการใช้งาน SQLSERVER ในระดับผู้เริ่มต้นจริงๆ ถ้าหากจะถามว่า Store Procedure ต่างจาก การเขียน Query Language ใน Application อย่างไรก็คงตอบได้ง่ายๆ คือ การใช้ Store Procedure จะลดการประมวลผลของ Web Server และ Application Server กรณีบน Windows Application ลดการทำงานอย่างไร ? Store Procedure นั้นจะถูก SQLSERVER คอมไพล์ให้เป็น Service หนึ่ง บน Database Server นั่นหมายความว่าถ้าหากถูกสร้างมาแล้ว ชุดคำสั่งจะถูกคอมไพล์ให้เป็นเหมือน Service บน Server เลยแต่กรณีที่เราเขียน Query Language ในโปรแกรมนั้น จะต้องถูกส่งไปให้คอมพิวเตอร์ประมวลผลคำสั่งทุกครั้ง ทั้งนี้ทั้งนั้นก็ขึ้นอยู่กลับความเหมาะสมของ Application ที่เราจะพัฒนาด้วย ถ้าเป็นกรณีมีผู้ใช้มากๆ ก็เหมาะที่จะเขียน Store Procedure แต่กรณีเขียนใช้งานเครื่องเดียวหรือใน Network ไม่กี่เครื่องผมก็ยังมองว่า ภาษา SQL นั้นยังสะดวกและง่ายในการจัดทำโปรแกรมมากกว่าการใช้งาน Store Procedure อยู่มากทีเดียว

การสร้าง Store Procedure
เริ่มดว้ยการใช้คำสั่ง CREATE PROCEDURE ตัวอย่างดังนี้


CREATE PROCEDURE Files_Insert

ด้วยคำสั่งง่ายๆ นี้ก็สามารถสร้าง Store Procedure ใน Database ได้แล้ว ต่อไปเราจะมาดูตัวอย่างการใช้งาน Parameter ให้กับชุดคำสั่งกันเลยนะครับ ตามตัวอย่างนี้


@p_FileID int output,
@p_FileText varbinary(MAX) = null,
@p_FileName varchar(50) = null,
@p_FileType varchar(50) = null

หลังจากได้ Parameter แล้วเราก็จะสร้าง Query ในการ Insert ข้อมูลกันตามนี้


INSERT INTO [Files]
(
[FileText],
[FileName],
[FileType]
)
VALUES
(
@p_FileText,
@p_FileName,
@p_FileType
)
SET @p_FileID = @@IDENTITY

สำหรับคำสั่ง @@IDENTITY ใช้ในกรณีกำหนดคีย์หลักให้ซ้ำกันไม่ได้ครับ
และภาพรวมของชุดคำสั่งใน Store Procedure จะเป็นตามนี้ครับ


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Description:
-- =============================================
CREATE PROCEDURE Files_Insert
-- Add the parameters for the stored procedure here
@p_FileID int output,
@p_FileText varbinary(MAX) = null,
@p_FileName varchar(50) = null,
@p_FileType varchar(50) = null
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO [Files]
(
[FileText],
[FileName],
[FileType]
)
VALUES
(
@p_FileText,
@p_FileName,
@p_FileType
)
SET @p_FileID = @@IDENTITY

END
GO

สำหรับตัวอย่างอื่นๆ เช่น การ Update Data ดังนี้


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Description:
-- =============================================
CREATE PROCEDURE Files_Update
-- Add the parameters for the stored procedure here
@p_FileID int,
@p_FileText varbinary(MAX) = null,
@p_FileName varchar(50) = null,
@p_FileType varchar(50) = null
AS
BEGIN
SET NOCOUNT ON;

UPDATE [Files] SET
[FileText]=@p_FileText
[FileName]=@p_FileName,
[FileType]=@p_FileType

WHERE
[FileID]=@p_FileID
END
GO

การ Delete Data ดังนี้


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Description:
-- =============================================
CREATE PROCEDURE Files_Delete
-- Add the parameters for the stored procedure here
@p_FileID int
AS
BEGIN
SET NOCOUNT ON;

DELETE FROM[Files] SET
WHERE
[FileID]=@p_FileID
END
GO

การ Find Data ดังนี้


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Description:
-- =============================================
CREATE PROCEDURE Files_Find
-- Add the parameters for the stored procedure here
@p_FileID int
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM[Files]
WHERE
[FileID]=@p_FileID
END
GO

สรุป
การใช้งานชุดคำสั่งเบื้องต้นในการสร้าง Store Proceduce ก็เป็นอย่างนี้ครับ สำหรับผมนั้นได้ทำ Database Design แล้วด้วยนั้น ยิ่งได้ทำทุกงานเลยครับ แล้วจะรวบรวมตัวอย่างอื่นๆ หรือแปลกๆ มาให้ดูกันนะครับ อย่าลืมติดตามด้วย

อ้างอิง
ผมได้นำตัวอย่างบางส่วนมาจากบทความ How to Write Store Procedure (SP) in SQL Server
Author: DevASP Team

ซึ่งต้องขออภัยที่จำ Link ไม่ได้ (I can’t remember this link)

ref : http://www.codemarts.com/2010/06/%E0%B8%81%E0%B8%B2%E0%B8%A3%E0%B9%80%E0%B8%82%E0%B8%B5%E0%B8%A2%E0%B8%99-store-procedure-%E0%B8%9A%E0%B8%99-sqlserver/