รู้จักกับ  Stored Procedure ใน Microsoft SQL Server

สำหรับบทความนี้ จะพาท่านผู้อ่านมารู้จักกับ  Stored Procedure  ซึ่งเป็นเนื้อหาส่วนหนึ่งของ หลักสูตร “SQL Server Programming - Stored Procedure” ที่ผู้เขียนบรรยายอยู่
 
คำอธิบายที่ง่ายที่สุดสำหรับ Stored Procedure  ก็คือ “การรวบรวมชุดคำสั่ง T-SQL เข้าด้วยกัน แล้วจัดเก็บเป็น Object ไว้ในฐานข้อมูล ผู้ใช้ที่มีสิทธิ์สามารถเรียกใช้งาน Stored Procedure ได้"
โดยชุดคำสั่งที่รวบรวมเข้าด้วยกันนั้น ประกอบด้วย

• คำสั่งแทบทุกคำสั่งบน Microsoft SQL Server ไม่ว่าจะเป็นการปรับปรุงข้อมูล หรืองานดูแลระบบ (แต่ก็ยังมีบางคำสั่งที่ไม่สามารถบรรจุใน Stored Procedure ได้)
• การประกาศ อินพุตและเอาต์พุตพารามิเตอร์ การประกาศตัวแปรภายใน ขึ้นมาเพื่อรับส่งค่า
• ตัวดำเนินการตามเงื่อนไข

 

ประโยชน์ของ Stored Procedure

1. ประโยชน์ด้านประสิทธิภาพการทำงาน
   • การเรียกใช้ Stored Procedure จะประหยัด Network Traffic มากกว่าการเรียกใช้ที่ละคำสั่ง T-SQL จากฝั่ง Client มายัง Server
   • คำสั่ง T-SQL ต้องถูก Compiled ก่อนถึงจะ Execute ได้ ผู้อ่านสามารถกลับไปศึกษาเกี่ยวกับ Compiled Plan ได้จากบทความ “เตรียมพร้อมก่อนอ่าน Execution Plan ตอนที่ 1”  Compiled Plan ของ Stored Procedure จะถูก Cached ไว้ และนำกลับมาประมวลผลทันทีที่มีการเรียกใช้ Stored Procedure นั้นซ้ำ ไม่ต้อง Compile ใหม่ เป็นการลดขั้นตอนการประมวลผลลง
2. ประโยชน์ด้านความปลอดภัย

ผู้เขียนขอยกตัวอย่างง่าย ๆ เช่น การปรับปรุงข้อมูลเงินเดือนพนักงานหากอนุญาตให้เข้าถึงตาราง Employee ได้โดยตรง
ผู้ใช้ที่ได้รับสิทธิ์เข้าถึงตารางจะสามารถเข้าถึงข้อมูลส่วนบุคคลอาทิ ข้อมูลเงินเดือน, ที่อยู่, ชื่อคู่สมรส หรือชื่อบิดามารดา เป็นต้น
แต่หากเราสร้าง Stored Procedure ที่ใช้สำหรับการปรับเงินเดือน โดยรับอินพุตพารามิเตอร์ เพียงรหัสพนักงานและเปอร์เซ็นเงินเดือนที่ปรับขึ้น
จากนั้นเราไม่อนุญาตให้ผู้ใช้เข้าถึงตารางโดยตรง แต่ให้เรียกใช้ Stored Procedure ตัวนี้แทนจะดีกว่าหรือไม่

เนื่องจาก Stored Procedure เป็นหนึ่งใน Securable (Database Object ที่สามารถกำหนดสิทธิ์การใช้งานได้)
จึงสามารถกำหนดสิทธิ์ให้ Database User หรือ Database Role ใดสามารถเรียกใช้ได้

3. การเขียนโปรแกรมแบบโมดูลาร์

Stored Procedure สามารถถูกเรียกใช้ซ้ำจากแอพพลิเคชั่น กี่ครั้งก็ได้ จึงสะดวกในแง่ของการนำโค๊ดกลับมาใช้ซ้ำ
ไม่ต้องเขียนในแอพพลิเคชั่นที่มีโค๊ดซ้ำกันหลายจุด อีกทั้งเวลาแก้ไขโค๊ดก็ทำการแก้แค่ใน Stored Procedure เพียงจุดเดียวเช่นกัน
 
ภาษาที่ใช้ในการพัฒนา Stored Procedure
บน Microsoft SQL Server สามารถสร้าง Stored Procedure ได้สองวิธี

1. สร้างตามปกติด้วยภาษา T-SQL
2. สร้างผ่าน Managed .NET Code

การสร้างผ่าน Managed .NET Code สามารถทำได้ผ่าน Visual Studio โดยเลือก Project Template เป็น SQL Server
จากนั้นจะสามารถเลือก Add New Item เป็นชนิด Stored Procedure ที่เขียนผ่าน SQL CLR (ภาษา Visual Basic) หรือ SQL CLR C# CLR (ภาษา Visual C#)

การสร้าง Stored Procedure ด้วย Managed .NET Code เป็นการเปิดโอกาสให้ผู้พัฒนาสามารถนำเอา .NET Library ที่มีมากมายมาเสริมความสามารถให้แก่ Microsoft SQL Sever
ในหลักสูตร “Database Development using Microsoft SQL Server” จะมีในส่วนนี้ให้ผู้อบรมได้ฝึกหัดเช่นกัน
 

คำสั่งที่อยู่ใน Stored Procedure ไม่ได้

ผู้เขียนมักบอกกับผู้อบรมว่าแทบทุกคำสั่ง T-SQL สามารถนำมาบรรจุเป็นชุดคำสั่งใน Stored Procedure ได้
ดังนั้น Stored Procedure จึงสามารถตอบโจทย์การใช้งานได้หลากหลาย

ตัวอย่างเช่น ใช้ในการปรับปรุงข้อมูล และรวมถึงอาจประกาศ Transaction เพื่อปรับปรุงข้อมูล, ใช้ในการสืบค้นข้อมูล ซึ่งสามารถแสดงหลาย Result Set ได้ หรือใช้ในงานดูแลระบบ เป็นต้น
แต่ก็มีบางคำสั่งไม่สามารถบรรจุลงใน Stored  Procedure ได้ เช่น

• USE <ชื่อฐานข้อมูล>
• CREATE AGGREGATE
• CREATE DEFAULT
• CREATE RULE
• CREATE SCHEMA
• CREATE or ALTER FUNCTION
• CREATE or ALTER PROCEDURE
• CREATE or ALTER TRIGGER
• CREATE or ALTER VIEW
• SET PARSEONLY
• SET SHOWPLAN_ALL
• SET SHOWPLAN_TEXT
• SET SHOWPLAN_XML

แม้ว่าเราไม่สามารถสั่งย้ายฐานข้อมูลผ่านคำสั่ง USE ได้ (เพราะ Stored Procedure เป็น Database Object จึงต้องรันภายใต้ฐานข้อมูลตนเองอยู่เท่านั้น) แต่ก็สามารถอ้างอิงไปยัง Object บนฐานข้อมูลอื่น ๆ ผ่าน Fully qualified name หรือ Four-part name ดังนี้  
<server name>.<database name>.<schema name>.<object name>
ส่วนบรรดาคำสั่ง CREATE ตาราง ๆ ยกเว้น CREATE TABLE ก็ไม่สามารถบรรจุลงใน Stored  Procedure ได้ แต่หากต้องการจริง ๆ ก็สามารถเลี่ยงได้โดยใช้การเขียนแบบ Dynamic SQL สำหรับคำสั่งเหล่านี้
คำสั่ง SET ที่ทำให้ Result Set ไม่แสดงในรูปแบบตาราง ก็ไม่สามารถบรรจุลงใน Stored  Procedure เช่นกัน
 

Stored Procedure เทียบกับ Object ชนิดอื่น

ผู้เขียนจะใช้งานฐานข้อมูล AdventureWorksLT2017 (Lightweight) เป็นตัวอย่าง
ผู้อ่านสามารถไปดาวน์โหลดไฟล์ AdventureWorksLT2017.bak จาก URL นี้ https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
โดย Object ต่าง ๆ ที่จะเปรียบเทียบมีดังต่อไปนี้

• Stored Procedure เทียบกับ View
• Stored Procedure เทียบกับ Function
  • Scalar Function
  • Table Valued Function

 

Stored Procedure เทียบกับ View

View เป็น Securable (Database Object ที่สามารถกำหนดสิทธิ์การใช้งานได้) ในฐานข้อมูลเช่นกัน
ปกติเวลาผู้เขียนบรรยายการสร้างวิว ก็มักจะบอกว่าผู้อบรมจากเขียนคำสั่ง SELECT สลับซับซ้อนแค่ไหนก็เขียนไป
พออยากให้ Query ที่เขียนกลายเป็น VIEW ก็ให้ประกาศคำสั่ง CREATE VIEW ปะไปด้านบนของ Query แล้วรันก็จะสร้างเป็น VIEW แล้ว
อันที่จริงมีข้อจำกัดอยู่บ้างบางประการที่ต้องคำนึงถึง แต่ไม่ข้อกล่าวในบทความนี้

ผู้เขียนจะหยิบยกรายละเอียดของ VIEW ที่ Microsoft สร้างไว้ให้แล้วในฐานข้อมูล AdventureWorksLT2017 ชื่อ SalesLT.vProductAndDescription มาปรับเพิ่มประโยค WHERE เข้าไปดังนี้

CREATE OR ALTER VIEW SalesLT.vProductAndDescription
AS
SELECT
               p.ProductID
,              p.Name
,              pm.Name AS ProductModel
,              pmx.Culture
,              pd.Description
FROM SalesLT.Product p
INNER JOIN SalesLT.ProductModel pm
ON p.ProductModelID = pm.ProductModelID
INNER JOIN SalesLT.ProductModelProductDescription pmx
ON pm.ProductModelID = pmx.ProductModelID
INNER JOIN SalesLT.ProductDescription pd
ON pmx.ProductDescriptionID = pd.ProductDescriptionID
WHERE Culture='en';

จากนั้นผู้เขียนจะทำการสร้าง Stored Procedure ชื่อ SalesLT.udpProductAndDescription โดยใช้รายละเอียดของ View เป็นต้นแบบดังนี้

CREATE OR ALTER PROCEDURE SalesLT.udpProductAndDescription
AS
SELECT
               p.ProductID
,              p.Name
,              pm.Name AS ProductModel
,              pmx.Culture
,              pd.Description
FROM SalesLT.Product p
INNER JOIN SalesLT.ProductModel pm
ON p.ProductModelID = pm.ProductModelID
INNER JOIN SalesLT.ProductModelProductDescription pmx
ON pm.ProductModelID = pmx.ProductModelID
INNER JOIN SalesLT.ProductDescription pd
ON pmx.ProductDescriptionID = pd.ProductDescriptionID
WHERE Culture='en';

เนื่องจาก VIEW เป็นนิพจน์ตาราง (นิพจน์ที่แสดงผลในรูปแบบตาราง) ชนิดหนึ่ง จึงประกาศหลังประโยค FROM แทนตารางได้ ดังแสดง

และสามารถนำไป JOIN กับตารางอื่น ๆ ได้อีกด้วย ดังแสดง

แต่หากเป็น Stored Procedure การเรียกใช้จะทำผ่านคำสั่ง EXECUTE ดังแสดง

การจะนำ Result Set ที่ได้ไป JOIN กับ ตารางอื่นไม่สามารถทำได้เหมือน VIEW จำเป็นต้องสร้าง Table Variable หรือ Temporary Table อย่างใดอย่างหนึ่งมารับ Result Set จาก Stored Procedure แล้วจึงนำไป JOIN กับตารางอื่น
 
ผู้เขียนขอแสดงตัวอย่างเป็น Table Variable หรือตัวแปรชนิดตารางดังนี้

DECLARE @varProduct TABLE
               (
                              ProductID int NOT NULL,
                              Name nvarchar(50) NOT NULL,
                              ProductModel dbo.Name NOT NULL,
                              Culture nchar(6) NOT NULL,
                              Description nvarchar(400) NOT NULL
               )
 
INSERT INTO @varProduct
 
EXEC SalesLT.udpProductAndDescription;
 
               OD.SalesOrderIDSELECT
,              P.Name as ProductName
,              P.Description as ProductDescription
,OD.OrderQty
FROM @varProduct as P
INNER JOIN SalesLT.SalesOrderDetail as OD
ON P.ProductID=OD.ProductID;

จะเห็นว่าต้องสร้างตัวแปรชนิดตารางขึ้นมาเสียก่อน จากนั้นจึงเรียกใช้ Stored Procedure เพื่อให้เกิด Result Set ใส่ลงในตัวแปรชนิดตาราง จากนั้นตัวแปรชนิดตารางถือเป็นนิพจน์ตารางประกาศหลังประโยค FROM ได้ จึงสามารถนำไป JOIN ต่อไป ผลลัพธ์ที่ได้ดังแสดง

จะเห็นว่า Stored Procedure ไม่สามารถใช้งานร่วมกับคำสั่ง SELECT ได้ตรง ๆ
หากต้องการผลลัพธ์จาก Stored Procedure ไป Query ต่อ ก็จะมีขั้นตอนยุ่งยาก แถวในแง่ของประสิทธิภาพก็ไม่ดีนัก

แต่หากดูในแง่ที่ Stored Procedure สามารถรับพารามิเตอร์ได้ จึงทำให้ Result Set ที่ได้มีความ Dynamic ตามพารามิเตอร์ที่รับเข้าไป
แต่ VIEW ไม่สามารถรับพารามิเตอร์ได้จึงไม่มีคุณสมบัติในเรื่องนี้ งั้นมาลองดูกัน

ผู้เขียนต้องการ Stored Procedure ที่แสดง Result Set ของใบสั่งซื้อ ตามเลขปีที่ป้อนเป็นพารามิเตอร์ ดังนี้

CREATE OR ALTER PROCEDURE SalesLT.udpGetOrderbyYear
               @YearNumber int
AS
               SELECT * FROM SalesLT.SalesOrderHeader as O
               WHERE  O.OrderDate>='Jan 1,'+CONVERT(char(4),@YearNumber)
                              AND O.OrderDate<'Jan 1,'+CONVERT(char(4),@YearNumber+1)


ตัวอย่างการเรียกใช้ Stored Procedure ดังแสดง

ถึงแม้ว่า Result Set ที่ได้จะมีความ Dynamic ขึ้น แต่การจะนำไป Query ต่อนั้นก็ยังมีความยุ่งยากเช่นเคย
จำเป็นต้องสร้าง Table Variable หรือ Temporary Table อย่างใดอย่างหนึ่งมารับ Result Set จาก Stored Procedure แล้วจึงนำไป Query ต่อ
 
จะดีกว่าไหมหากเปลี่ยนไปใช้ Table Value Function ซึ่งเป็นนิพจน์ตารางแบบหนึ่ง
สามารถอ้างอิงหลังประโยค WHERE ดังนั้นจึงนำไป Query ต่อได้โดยง่าย ดังตัวอย่างนี้

CREATE OR ALTER FUNCTION SalesLT.udfGetOrderbyYear(@YearNumber int) RETURNS TABLE
AS
               RETURN
                              SELECT * FROM SalesLT.SalesOrderHeader as O
                              WHERE  O.OrderDate>='Jan 1,'+CONVERT(char(4),@YearNumber)
                                             AND O.OrderDate<'Jan 1,'+CONVERT(char(4),@YearNumber+1)

ตัวอย่างการเรียกใช้ Table Value Function ดังแสดง

จะเห็นว่า Table Value Function สามารถเขียนร่วมในคำสั่ง SELECT ได้เลยจึงสะดวกกว่าการเรียกใช้ Stored Procedure มาก
 

Stored Procedure เทียบกับ Function

ตัวอย่างก่อนหน้า ผู้เขียนก็ได้เปรียบเทียบ Stored Procedure ที่แสดงผลเป็นตาราง กับ Table Value Function ซึ่งแสดงผลเป็นตารางเช่นกัน ว่าใช้งานยากง่ายต่างกัน
ในส่วนนี้ผู้เขียนจะพูดถึงภาพรวมความเหมือนกันและต่างกันของ Stored Procedure และ Function

ก่อนอื่นทั้ง Procedure และ Function คือการเขียนโปรแกรมย่อยในการเขียนโปรแกรมแบบโมดูลาร์ และ Function เป็น Securable
(Database Object ที่สามารถกำหนดสิทธิ์การใช้งานได้) ในฐานข้อมูลเช่นกัน

โดยปกติแล้วความต่างหลัก ก็คือ Procedure มักไม่ค่อยเขียนให้เกิดการคืนค่ากลับออกมา
แต่ Function นั้นบังคับให้ต้องคืนค่ากลับเสมอ แต่หากต้องการให้ Procedure คืนค่ากลับก็สามารถประกาศพารามิเตอร์ที่ส่งออกข้อมูลได้
และที่ต่างกันชัดเจนมากที่สุด 2 ประการคือ

1. Function ใช้เพื่อปรับปรุงข้อมูล ไม่ได้ ดังนั้นคำสั่ง INSERT, UPDATE และ DELETE ไม่สามารถ อยู่ใน Function ได้
2. ภายใน FUNCTION ไม่สามารถ ดักรับข้อผิดพลาดได้

แต่ทั้งสองอย่างนี้สามารถทำได้ใน Procedure

บทความนี้ผู้เขียนจะไม่ลงรายละเอียดการเขียนสคริปต์เพื่อนำมาสร้างเป็น Procedure ผู้สนใจสามารถฟังบรรยายการเขียนสคริปต์ได้จากหลักสูตร “SQL Server Programming - Stored Procedure”
การนำสคริปต์ไปสร้างเป็น Stored Procedure และ Function ได้จากหลักสูตรดังกล่าวเช่นกัน
หากจะเปรียบเทียบโปรแกรมย่อยทั้งแบบ Stored Procedure และ Function ต้องให้มีการคืนค่ากลับ ค่าที่คืนกลับมาแบ่งได้ 2 ชนิดคือ

1. คืนค่าค่าเดียว หรือ Scalar Value
2. คืนค่าหลายค่า หรือ Table Value (ในภาษาโปรแกรมมิ่งอื่น อาจเรียก Array Value)

เพื่อความง่ายต่อการทำความเข้าใจ สรุปแบบนี้เลยก็แล้วกันว่า หากไม่ได้คืนค่าเป็น Table ก็เป็นค่าแบบ Scalar หมด
 

Store Procedure เทียบกับ Scalar Function

ผู้เขียนจะยกตัวอย่าง Scalar Function ที่คืนค่าออกมาเป็นเลขที่สั่งซื้อใบล่าสุดของปีที่ป้อนเข้าไป ดังนี้

CREATE OR ALTER FUNCTION SalesLT.udfGetLastOrderIDbyYear (@yearnumber int) RETURNS int
AS
BEGIN
               DECLARE @result int
 
               SELECT @result=MAX(O.SalesOrderID) FROM SalesLT.SalesOrderHeader as O
               WHERE  O.OrderDate>='Jan 1,'+CONVERT(char(4),@yearnumber)
               AND O.OrderDate<'Jan 1,'+CONVERT(char(4),@yearnumber+1)
              
               RETURN @result
END;

ตัวอย่างการเรียกใช้ Function ดังแสดง

จะเห็นว่าฟังก์ชั่นดังกล่าวรับค่าเป็น int และคืนค่ากลับออกมาเป็น int เมื่อเรียกใช้สามารถนำไปเขียนเป็นส่วนหนึ่งของการ Query ได้เลย
จากตัวอย่างเมื่อใส่เลขปี 2008 เข้าไป Function จะคืนค่าออกมาเป็น 71946 จากนั้นนำเอาเลขที่ได้ไปกรองตาราง SalesLT.SalesOrderDetail
 
แต่หากเขียนโปรแกรมย่อยดังกล่าวอยู่ในรูปแบบ Stored Procedure ดังนี้

CREATE OR ALTER PROCEDURE SalesLT.udpGetLastOrderIDbyYear
               @yearnumber int
,              @LastOrderID int OUTPUT
AS
               SELECT @LastOrderID=MAX(O.SalesOrderID) FROM SalesLT.SalesOrderHeader as O
               WHERE  O.OrderDate>='Jan 1,'+CONVERT(char(4),@yearnumber)
               AND O.OrderDate<'Jan 1,'+CONVERT(char(4),@yearnumber+1);

จะเห็นว่าต้องประกาศพารามิเตอร์ที่คืนค่าออกชื่อว่า @LastOrderID ซึ่งการจะนำค่าที่คืนออกไปใช้ ต้องประกาศตัวแปรให้ตรงชนิดกันมารับค่าก่อน จากนั้นค่อยในตัวแปรไปใช้ต่อ ดังนี้

DECLARE @varLastOrderID int
 
EXEC SalesLT.udpGetLastOrderIDbyYear @yearnumber=2008, @LastOrderID=@varLastOrderID OUTPUT
 
SELECT
*
FROM SalesLT.SalesOrderDetail as OD
WHERE OD.SalesOrderID=@varLastOrderID;

ตัวอย่างการเรียกใช้ Stored Procedure ดังแสดง

จะเห็นว่าสามารถเขียนให้ทั้ง Procedure และ Function แสดงผลเหมือนกันได้ แต่วิธีเรียกใช้งานต่างกัน
 

Store Procedure เทียบกับ Table Valued Function

ผู้เขียนได้เรื่องนี้ละเอียดมากแล้วตอนเทียบกับ VIEW และได้บอกไว้ว่ากรณี Stored Procedure นั้น จำเป็นต้องสร้าง Table Variable หรือ Temporary Table อย่างใดอย่างหนึ่ง เพื่อรับ Result Set จาก Stored Procedure ไปใช้ต่อ โดยยกตัวอย่างเป็นการใช้ Table Variable แต่ในส่วนนี้ผู้เขียนขอใช้ Temporary Table บ้างเพื่อให้บทความมีความหลากหลาย
 
ผู้เขียนจะยกตัวอย่าง Table Valued Function ชนิด Inline (Function ที่มีคำสั่งอยู่ภายในเพียงคำสั่งเดียว) ที่คืนค่าออกมาเป็นตารางรายการสินค้า ตามหมายเลข Category ที่ป้อนเข้าไป ดังนี้

CREATE OR ALTER FUNCTION SalesLT.udfGetProductByCatID(@CatID int, @Rowno int)
RETURNS TABLE
AS
               RETURN
                              SELECT TOP(@Rowno)
                                             *
                              FROM SalesLT.Product as P
                              WHERE P.ProductCategoryID=@CatID
                              ORDER BY P.ListPrice DESC

ตัวอย่างการเรียกใช้ Function ดังแสดง

หากเขียนโปรแกรมย่อยดังกล่าว อยู่ในรูปแบบ Stored Procedure ดังนี้

CREATE OR ALTER PROCEDURE SalesLT.udpGetProductByCatID
               @CatID int
,              @Rowno int
AS
               SELECT TOP(@Rowno)
                              *
               FROM SalesLT.Product as P
               WHERE P.ProductCategoryID=@CatID
               ORDER BY P.ListPrice DESC
               ;

การเรียกใช้ Stored Procedure ไม่สามารถเขียนร่วมใน Query เดียวกันแบบ Function ได้ จำเป็นต้อง Execute เพื่อให้ได้ Result Set ออกมาเก็บไว้ก่อน
คราวนี้ผู้เขียนขอใช้ Temporary Table บ้าง เมื่อได้ Temporary Table แล้วจึงนำไปเขียน Query ต่ออีกครั้งหนึ่ง ดังนี้

CREATE TABLE #TempProduct
(
               ProductID int NOT NULL,
               Name nvarchar(50) NOT NULL,
               ProductNumber nvarchar(25) NOT NULL,
               Color nvarchar(15) NULL,
               StandardCost money NOT NULL,
               ListPrice money NOT NULL,
               Size nvarchar(5) NULL,
               Weight decimal(8, 2) NULL,
               ProductCategoryID int NULL,
               ProductModelID int NULL,
               SellStartDate datetime NOT NULL,
               SellEndDate datetime NULL,
               DiscontinuedDate datetime NULL,
               ThumbNailPhoto varbinary(max) NULL,
               ThumbnailPhotoFileName nvarchar(50) NULL,
               rowguid uniqueidentifier NOT NULL,
               ModifiedDate datetime NOT NULL
);
 
INSERT INTO #TempProduct
EXEC SalesLT.udpGetProductByCatID @CatID=25,@Rowno=5;
 
SELECT
               P.ProductNumber
,              P.Color
,              P.ListPrice
,              D.Description
FROM #TempProduct as P
INNER JOIN SalesLT.ProductModel as M
ON P.ProductModelID=M.ProductModelID
INNER JOIN SalesLT.ProductModelProductDescription as MD
ON M.ProductModelID=MD.ProductModelID
INNER JOIN SalesLT.ProductDescription as D
ON MD.ProductDescriptionID=D.ProductDescriptionID
WHERE MD.Culture='en';
 
DROP TABLE #TempProduct;

ตัวอย่างการเรียกใช้ Stored Procedure ดังแสดง

เช่นกันทั้ง Procedure และ Function แสดงผลออกมาเหมือนกันได้ แต่วิธีเรียกใช้งานต่างกัน
 

Stored Procedure เพื่องานดูแลระบบ

นอกจากการใช้งาน Stored Procedure จัดการกับข้อมูลในฐานข้อมูลแล้ว
บ่อยครั้งที่มักพบเจอผู้ดูแลระบบสร้าง Stored Procedure เพื่อง่ายต่อการดูแลระบบจัดการฐานข้อมูล
โดยอาจจะรวบรวมคำสั่งหลาย ๆ คำสั่งที่ต้องดำเนินการต่อเนื่องตามลำดับ สร้างไว้เป็น Procedure แล้วเรียกใช้จาก Stored Procedure เพียงจุดเดียว
หรือบ้างครั้งในส่วนของคำสั่งเป็นคำสั่งที่ซ้ำ ๆ  กัน แตกต่างกันที่ชื่อ Object ที่อ้างถึง อาจใช้การประกาศ Cursor ร่วมด้วย
 
ดังตัวอย่างนี้ ผู้ดูแลระบบต้องการปรับปรุง Statistics ที่มีการเลือก Sample 100 Percent (Full Scan)
โดยต้องการสืบค้นชื่อของตารางในทุกฐานข้อมูลที่ไม่ใช่ของระบบสร้างให้ โดยใช้ การประกาศ Cursor ซ้อนกัน ดังนี้

CREATE PROCEDURE dbo.UpdateAllStatistics as
BEGIN
               SET NOCOUNT ON
               DECLARE @dbname VARCHAR(100)
               DECLARE db_cursor CURSOR READ_ONLY FOR 
                              SELECT name
                              FROM master.sys.databases
                              WHERE name NOT IN      
( )  

 AND state = 0 AND is_in_standby = 0

 
               OPEN db_cursor  
               FETCH NEXT FROM db_cursor INTO @dbname;
 
               WHILE @@FETCH_STATUS = 0
               BEGIN
 
                              DECLARE @SchName VARCHAR(100),@TblName VARCHAR(100);
                              DECLARE @varTableName TABLE (SchName VARCHAR(100),TblName VARCHAR(100))
                              INSERT INTO @varTableName
                              EXEC(    '
                                                            SELECT
                                                                           SC.name as SchName
                                                            ,              T.name as TblName
                                                            FROM '+@name+'.sys.tables as T
                                                            INNER JOIN sys.schemas as SC
                                                            ON T.schema_id=SC.schema_id
                                                            WHERE type=''U'''
                                             )
                              DECLARE tb_cursor CURSOR READ_ONLY FOR 
                                             SELECT SchName,TblName FROM @varTableName
              
                              OPEN tb_cursor
                              FETCH NEXT FROM tb_cursor INTO @SchName, @TblName;
                              WHILE @@FETCH_STATUS = 0
                              BEGIN
                                             EXEC     (
                                                            'UPDATE STATISTICS '
                                                            +@dbname+'.'+@SchName+'.'+@TblName+' WITH FULLSCAN'
                                                            )
                                             FETCH NEXT FROM tb_cursor INTO @SchName, @TblName
                              END
                              CLOSE tb_cursor  
                              DEALLOCATE tb_cursor
                              FETCH NEXT FROM db_cursor INTO @name  
               END
               CLOSE db_cursor  
               DEALLOCATE db_cursor
END

 

สรุป

Stored Procedure นั้นมีข้อโดดเด่นคือสามารถบรรจุคำสั่งลงไปได้แทบทุกคำสั่ง
ทำให้มีอิสระในการนำใช้งานได้หลากหลาย รองรับการประกาศตัวแปร การควบคุมโฟลว์ภายในสคริปต์ การดักรับข้อผิดพลาดสามารถใช้เพื่อปรับปรุงข้อมูลได้ ซึ่งโปรแกรมย่อยด้วยกันอย่าง Function ทำได้น้อยกว่ามาก
แต่หากเป็นเรื่องของการแสดงผล Result Set ออกมา View และ Function ก็ควรเป็นหนึ่งตัวเลือกตามความเหมาะสมการใช้งาน
 
ในแง่ของประสิทธิภาพว่าหากแสดงผลเหมือนกันแล้ว แบบไหนดีกว่ากันผู้เขียนขอนำไม่กล่าวถึงในบทความนี้
อาจจะมีในบทความอื่นแยกต่างหาก แต่มีกล่าวถึงสิ่งเหล่านี้แน่นอนในหลักสูตรฝึกอบรมที่ผู้เขียนบรรยายอยู่

เช่น Compiled Plan ของ Stored Procedure  อาจทำให้ประสิทธิภาพถดถอยแทนที่จะดีขึ้นเหตุจาก Parameter Sniffing
โดย Parameter ที่ใช้ในการ Compiled Plan ส่งผลเสียต่อการ Execute คราวถัด ๆ ไป ,
View กับ Inline-Table Valued Function ดีกว่า Multi-Statement Table Valued Function เพราะเหตุใด
หรือ การฝัง Impersonate Login หรือ Impersonate User ลงใน Stored Procedure  และ Function เพื่ออะไร เป็นต้น
จนกว่าจะพบกันใหม่ ในภาวะ COVID-19 ขออวยพรให้ทุกคนอยู่รอดปลอดภัย