การเขียน store procedure บน ms sql server ตอนที่ 3 store procedure แบบที่มีการรับค่า parameter

หน้าแรก SQL Server การเขียน store procedure บน ms sql server ตอนที่ 3 store procedure แบบที่มีการรับค่า parameter

จากบทความตอนที่แล้วที่ได้กล่าวถึง รูปแบบ store procedure และตัวอย่างการเขียน store procedure แบบทั่วๆไป
สำหรับบทความในตอนที่ 3 นี้จะเป็นการเขียน store procedure แบบที่มีการรับค่า parameter นะครับ
มาดูตัวอย่างเริ่มต้นการเขียน Store Procedure แบบที่มีการรับค่า parameter กันนะครับ

Code

-- การสร้าง Stored Procedure แบบรับค่าParameter
Create Procedure sp_fullemployee_by_emp_code (
@parameter1 datatype ,
@parameter2 datatype ,
@parameter3 datatype ,

)
As
BEGIN
คำสั่ง Sql Statement
END

ตัวอย่างการสร้าง Store procedure แบบมี parameter นะครับ จะเป็นการสร้าง store procedure สำหรับ แสดงข้อมูล employee แบบที่มีการใส่เงื่อนไข ตาม emp_code ดังโคดด้านล่าง

Code

create procedure sp_fullemployee_by_emp_code (
@emp_code varchar(5)
)
AS
BEGIN
select
d.dep_code,d.dep_name,e.emp_code,e.emp_name,e.emp_surname,e.emp_tel,e.emp_email
from tbl_department d left join tbl_employee e on e.dep_code=d.dep_code
where e.emp_code=@emp_code
END
-- การเรียกใช้งาน Stored procedures แบบรับค่า parameter
exec sp_fullemployee_by_emp_code 'E0002'
-- หรือ ส่งค่าให้กับ parameter ที่ต้องการ โดยการกำหนดชื่อดังเช่นตัวอย่างด้านล่างก็ได้ ตัวอย่างนี้จะใช้บ่อยๆ ในกรณี สร้าง Store procedure แบบกำหนดค่าเริ่มต้นให้กับ parameter
exec sp_fullemployee_by_emp_code @emp_code='E0002'

2.1 Stored Procedures แบบกำหนดค่าเริ่มต้นให้ Parameter ในการสร้าง Store procedure แบบนี้จะเอาไว้สำหรับ Store procedure ที่มีการรับหลายๆ parameter และมี บาง parameter ที่ค่าข้อมูลเหมือนๆกัน จึงกำหนดเป็นค่าเริ่มต้นได้ จะทำให้เวลาเรียกใช้งาน จะได้ง่ายขึ้น ไม่ต้องใส่ parameter ให้ครบทุกตัว

Code

-- การสร้าง Stored Procedure แบบกำหนดค่าเริ่มต้นให้ Parameter
Create Procedure sp_fullemployee_by_emp_code (
@parameter1 datatype ,
@parameter2 datatype=Default Value,
@parameter3 datatype ,

)
As
BEGIN
คำสั่ง Sql Statement
END

ตัวอย่างเช่น การสร้าง Store procedure สำหรับเพิ่มข้อมูลพนักงานใหม่ (จากตัวอย่าง ผมจะแสดงให้ดูถึงข้อมูลจึงใช้คำสั่ง select @parameter นะครับ หากจะใช้จริง ก็เขียน ตรง Sql statement เป็น insert ไปแทน

Code

create procedure sp_insert_employee (
@emp_code varchar(5) ,
@emp_name varchar(50) ,
@emp_surname varchar(50) ,
@emp_tel varchar(20)='-' , -- กำหนดค่าเริ่มต้นเป็น -
@emp_email varchar(60)='-' , -- กำหนดค่าเริ่มต้นเป็น -
@dep_code varchar(2)='01' -- กำหนดค่าเริ่มต้นเป็นฝ่าย Account
)
AS
BEGIN
select @emp_code , @emp_name,@emp_surname,@emp_tel,@emp_email,@dep_code
END
-- การเรียกใช้งาน เมื่อเรา สร้าง Store procedure แบบกำหนดค่าเริ่มต้นให้ parameter ทำให้เรา จะใส่ข้อมูลใน parameter @emp_tel , @emp_email , @dep_code หรือไม่ก็ได้ เช่น
exec sp_insert_employee 'E0006','ทดสอบ','นามสกุล'
จากตัวอย่าง จะเห็นว่า ผมกรอกเข้าไปเพิ่มเพียง 3 parameter เท่านั้น ส่วนที่เหลือจะเป็นค่าเริ่มต้นที่กำหนดไว้

หรือ หากต้องการเปลี่ยนค่า ให้กับค่าเริ่มต้น บาง parameter เราก็สามารถทำได้เช่น
exec sp_insert_employee 'E0006','ทดสอบ','นามสกุล' ,@dep_code='02'
จากตัวอย่าง ผมต้องการเปลี่ยน @dep_code เท่านั้นก็สามารถทำได้ โดยการระบุชื่อ Parameter ที่ต้องการ แล้วใส่ค่า value เข้าไปเท่านั้นเองครับ

2.2 Stored Procedures แบบ Output Parameter ใช้ในกรณีที่เราต้องการ ให้ Store procedure มีการส่งค่าบางอย่างกลับมาให้เรานะครับ
ซึ่งการใช้วิธีนี้ เราสามารถ สร้างตัวแปร output ส่งค่ากลับมาได้หลายตัวแปร จะต่างจาก การสร้าง Store procedure แบบ return ซึ่ง ส่งค่ากลับมาได้ ตัวเดียวนะครับ

Code

-- การสร้าง Stored Procedure แบบ Output Parameter
Create Procedure sp_fullemployee_by_emp_code (
@parameter1 datatype ,
@parameter2 datatype=Default Value,
@parameter3 datatype output,

)
As
BEGIN
คำสั่ง Sql Statement
END

ตัวอย่างเช่น การสร้าง Store procedure สำหรับค้นหาข้อมูลพนักงาน ที่ต้องการค่า output คือจำนวนที่ค้นหาพบ ดังนั้นเราจึงต้องให้มี Output parameter เป็นจำนวนที่ค้นหาพบ จึงเขียน Store procedure ได้ดังนี้

Code

create procedure sp_search_employee(
@txtsearch varchar(50),
@ItemCount int output
)
AS
BEGIN
-- query ข้อมูล
select * from tbl_employee
where (emp_code + ' ' + emp_name + ' ' + emp_surname) like '%'+@txtsearch+'%'
-- นับจำนวน
set @ItemCount=(select count(*) from tbl_employee
where (emp_code + ' ' + emp_name + ' ' + emp_surname) like '%'+@txtsearch+'%'
)
END

การเรียกใช้งาน เนื่องจาก Store procedure แบบ output parameter นี้การเรียกใช้งาน เราจึงต้องมีการสร้างตัวแปร ขึ้นมาเพื่อรับ ค่า output ด้วย รูปแบบการเรียกใช้งานจึงเป็นเช่นนี้
Declare @count int
exec sp_search_employee 'ส', @count output

จะเห็นว่า เรามีการ Declare ตัวแปร @count มีชนิดเป็น int ชนิดเดียวกับ @ItemCount ที่อยู่ใน store procedure แล้ว เราจะใช้รูปแบบการกับคือ การ ใส่ตัวแปร แล้วเติม output เข้าไปในตำแหน่งของ output parameter ของ store procedure

เมื่อเรากำหนดข้อมูลได้เรียบร้อยแล้ว เราสามารถที่จะ select @count เพื่อเห็น ผลลัพย์ของ output parameter ได้นะครับ

select @count

refer: http://devzonedd.designweb2you.com/viewthread.php?tid=82&extra=page%3D1

ขึ้นไปด้านบน