วันอังคารที่ 13 พฤศจิกายน พ.ศ. 2555

Delegate & Event ใน C# เพื่อใช้ในการรับส่งค่าระหว่าง From สามารถนำไปใช้ได้


delegate ซึ่งแปลตรงตัวก็คือ 'ตัวแทน' โดยการทำงานของมันก็ทำหน้าที่เป็นตัวแทนของ method 
ลักษณะการใช้งาน Delegate ซึ่งโดยปกติการเรียกใช้ method นั้นเราต้องระบุชื่อ method ที่เราต้องการใช้งาน แต่แนวคิดในการโปรแกรมแบบ Delegate นั้นต่างออกไป โดยแทนที่เราจะเรียกไปที่ methodที่เราต้องการ ตรงๆแต่กลับเรียกผ่าน Delegate แทน   ซึ่ง Delegate ก็คือตัวแทน method ที่เราต้องการนั่นเอง  โดยผู้เรียก delegate จะไม่รับรู้ว่า method จริงอยู่ที่ส่วนใดของโปรแกรม แค่คิดว่าถ้าเรียกผ่าน Delegate แล้ว Delegate จะต้องมีหน้าที่ทำงานได้ตามที่ผู้เรียกต้องการเท่า
Ex:    ผู้เรียก -------------------------> Delegate -------------------------> Class1.fnc();
การประกาศ Delegate  
delegate void Mydelegate ();
จุดประสงค์ของประโยคด้านบนคือ ประกาศDelegate type ชื่อ Mydelegate ซึ่งไม่มี argument และมี return type เป็น void

 เบื้องหลังการทำงานนั้น compiler จะสร้าง metadata และ class ที่สืบทอดมาจาก MulticastDelegatให้อย่างอัตโนมัติ ซึ่งสามารถแสดงดัง psuedocode 
class Mydelegate : MulticastDelegate
{      
   public Mydelegate(object obj, int ptr)
   { }
   public void Invoke()
   { }
}


เรามาเริ่มเขียน Code  ในการรับส่งข้อมูล  โดยใช้  delegate  เป็น ตัวแทนของ Method By Event กันครับ 
จาก  Flow การทำงานดังกล่าว 
     ผู้เรียก -------------------------> Delegate -------------------------> Class1.fnc();
เราจะทำการสร้าง  From มา 2 From   โดย
ผู้เรียก  ==> FrmSale.cs   (หน้าจอขายเพื่อแสดงรายการสินค้า)
ผู้ถูกเรียก ===> Frm_Find.cs (หน้าจอที่แสดงรายการสินค้า เพื่อ Set กลับมาที่หน้าจอขาย)
Delegate  ===> จะเป็น Object  ที่อยู่ในหน้าจอ   Frm_Find.cs
Class1.fnc() ==>จะเป็น Class ที่ Delegate  Pass  ค่าเข้าไปให้  และ ถูก   FrmSale.cs นำข้อมูลกลับผ่าน Event 

เอาละ  เรามาลอง Code ในแต่ละส่วนกัน  มาเริ่มที่   Frm_Find.cs  ก่อนนะครับ 


CODE#:  Frm_Find.cs 


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace TransForm
{
       public partial class Frm_Find : Form
      {
          // IdentityProduct   คือ Class (Class1.fnc())  ที่ใช้  Delegate  เป็นตัวแทน 
           public delegate void IdentityReturnHandler(object sender, IdentityProduct e);
          // Identitydata เป็น Event ของ Form(Frm_Find)  เพื่อที่จะ Pass Valuse กลับไปยังผู้ที่เรียก
           public event IdentityReturnHandler Identitydata;       

         /// 
         ///  ======> ในส่วนนี้เป็นแค่ตัวอย่างนะครับ  ที่จริงต้อง Return กลับเป็น  DataRow หรือ DataSet
         /// 
         /// 
         /// 
        private void btn_OK_Click(object sender, EventArgs e)
        {
            try
             {
                    string  ls_Name  = txt_Name.Text;
                    Decimal  ld_Price  = Convert.ToDecimal(txt_Price.Text)   
//สังเกตุIdentityProduct ให่ดีนะครับมันจะถูกสร้างเป็น Type Event ใน   ตัว delegate 
                    // เพราะเวลาที่ตัวเรียกใช้  จะเรียกผ่าน Event 
                    IdentityProduct arge = new IdentityProduct( ls_Name, ld_Price);  // ส่งค่าไปยัง Class   IdentityProduct  
                    Identitydata(this, arge);
                    this.Dispose();
             }
            catch { }
         } 
}

   /// 
    ///  เป็น CLASS ที่ทำการส่งค่ากลับไปให้ From Sale โดยที่ตัว Delegate  เป็นตัวแทน โดบ Return  กลับผ่าน Event
    /// 
    public class IdentityProduct : System.EventArgs
    {
        string  ls_Name  = String.Emtry;
        Decimal  ld_Price  =0;        
       
        // เป็น Constructure ของ Class  รับค่าได้อย่างเดียว
        public IdentityProduct( string  Name ,Decimal  Price)
        {
             ls_Name  = Name;
             ld_Price   = Price;
        }


       // เป็น Property ที่จะทำการ Return กลับไปโดย Event 
        public string  Name
        {
            get { return ls_Name; }
        }

        public Decimal  PRICEPRODUCT
        {
            get { return ld_Price; }
        }
    }    
}


CODE#:  FrmSale.cs

มาดูตอนที่เรียกครับ สร้าง From มา 1 From จากนั้นก้อสร้าง ปุ่ม


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace TransForm
{
   public partial class FrmSale : Form
    {
     private void btn_Search_Click(object sender, EventArgs e)
        {
              Frm_Find _F = new Frm_Find();
             // บรรทัดล่างนี้จะเป็นการเรียกใช้งานผ่าน Event ที่อยู่ใน From   Frm_Find
            _F.Identitydata+=new Frm_Find.IdentityReturnHandler(_F_Identitydatarow);
            _F.ShowDialog();
        } 
// Methodนี้จะถูกCreate ขึ้นมาตอนที่เราเรียกใช้  Event _F.Identitydata+=new Frm_Find.IdentityReturnHandler   (_F_Identitydatarow);
         void _F_Identitydata(object sender, IdentityProduct e)
        { 
             //  จากนั้น ตัวแปลจาก From ก็ ถูกส่งผ่าน Class  IdentityProduct   ใน Event e  เพราะตอนที่เรา  delegate  เราให้มันเป็น Event 
             Txt_Name.Text  = e.Name;
             Txt_Price.Text   = e.PRICEPRODUCT.Tostring();
        }
    }
}

Credit: WINCOMMON

วันพฤหัสบดีที่ 25 ตุลาคม พ.ศ. 2555

การสร้าง Function ใน SQL Server 2008


T-SQL Functions มี 3 แบบ

Scalar : data types ที่คืนค่ากลับมาได้เป็น Text, ntext, image แต่ไม่สนับสนุน timestamp

CREATE FUNCTION dbo.getFac
(
@SID nvarchar(3)
)
RETURNS /* datatype */ nvarchar(120)
AS
BEGIN
declare @name nvarchar(120)
select @name = FacName from Faculty where FacID=@SID 
RETURN /* value */@name
END

การนำไปใช้งาน
SELECT DeptID, DeptName, FacID, dbo.getFac(FacID) AS FacName
FROM Dept



Inline Table-Valued : คืนค่า table data type เป็นเหมือนการนำเอา Scalar Function มาใช้ในการดึงค่าใน table มาแสดง คล้ายๆการสร้าง view เพื่อแสดง report ที่ต้องการได้

CREATE FUNCTION dbo.TableDeptFacName
(
@FacID nvarchar(3)
)
RETURNS TABLE
AS
RETURN 
SELECT DeptID, DeptName, FacID, dbo.getFac(FacID) AS FacName
FROM Dept
WHERE FacID=@FacID

การนำไปใช้งาน
SELECT DeptID, DeptName, FacID, FacName
FROM dbo.TableDeptFacName('002') AS TableDeptFacName_1

002 คือรหัสคณะที่ส่งเข้าไป


Multi-Statement : เป็นการคล้ายการสร้าง temp table ตามที่ต้องการ สามารถ pass parameter เข้าไปได้

CREATE FUNCTION dbo.TableTmpDeptName
(
@FacID nvarchar(3)
)
RETURNS @DeptFacNameTB TABLE ([DeptID] [nvarchar] (4),[DeptName] [nvarchar] (120))
AS
BEGIN
INSERT INTO @DeptFacNameTB
SELECT DeptID,DeptName FROM Dept WHERE FacID=@FacID
DECLARE @cnt INT
SELECT @cnt = COUNT(*) FROM @DeptFacNameTB
IF @cnt = 0
INSERT INTO @DeptFacNameTB (
[DeptID],
[DeptName] )
VALUES ('000','No Department In This Faculty')
RETURN
END

การนำไปใช้งาน
SELECT DeptID, DeptName
FROM dbo.TableTmpDeptName('000') AS TableTmpDeptName_1


** ข้อดีการทำงานทั้งหมดใน function อยู่ใน Database Server ถ้าต้องการแก้ไขใดก็สามารถทำได้ที่ Server

Credit: Theerayuth@My KM

การเขียน store procedure


การเขียน store procedure บน ms sql server ตอนที่ 1


Store Procedure คืออะไร?
     หลายคนถามว่า Store Procedure คืออะไร? ผมก็จะขอนิยามความหมายซักนิดนะครับ Store Procedure นั้นเป็นโปรแกรมที่เก็บอยู่ในฐานข้อมูลนั่นเอง ซึ่งการจะเขียน Store procedure นั้นเราจะต้องดูด้วยว่า โปรแกรมฐานข้อมูล ที่เราใช้อยู่นั้นมี ส่วนของ Store procedure อยู่ด้วยหรือเปล่า ส่วนใหญ่จะมีกับฐานข้อมูลใหญ่ เช่น Oracle , Ms sql server , Mysql เวอร์ชั่นใหม่ๆ และอื่นๆ

ทำไมเราถึงจำเป็นต้องใช้ Store Procedure 
     ถามว่าทำไมต้องใช้ อันนี้ที่จริงแล้ว ถ้าไม่ใช้ เราก็ยังสามารถเขียนโปรแกรม ติดต่อดาต้าเบสดึงข้อมูลได้เหมือนกัน ถ้าเรา ใช้คำสั่ง select * from table ดึงข้อมูลเพียง table เดียวคำสั่งสั้นๆ คงไม่เห็นถึงความแตกต่างเท่าไหร่ แต่เมื่อไหร่ ที่ต้องมีการทำงาน ในหลายๆ table พร้อมๆกัน ซัก 4-5 table เราต้อง join table อีกเพียบ หรืออาจจะมี sub query อยู่ด้วย ถ้าเมื่อไหร่ เราต้องทำงานแบบนี้ การส่งคำสั่ง Query ปริมาณมหาศาล ทำให้เกิด Traffic ระหว่าง application กับ database ขนาดใหญ่มาก และส่งผลให้ โปรแกรมเราทำงานช้าจนน่าเกลียดเลยทีเดียว 

แต่หากเราใช้ store procedure ในการดึงข้อมูล จะลดปัญหา Traffic ไปได้มาก เนื่องจาก Store Procedure นั้นเป็น Database object ที่จะเก็บในรูปแบบ Compile แล้วและมีการทำงานแบบ Sql Statement เรียบร้อย การทำงานจะตกอยู่กับ database server ดังนั้นเราจึงสามารถใช้คำสั่ง Query ได้หลายตัวพร้อมๆกัน เพื่อให้ได้ผลลัพธ์ตามที่เราต้องการ จึงทำให้ performance ของโปรแกรมเราดีขึ้นอย่างมากมาย

การเขียน Store procedure ใน Ms Sql Server นั้นเราจะใช้ภาษา T-sql ในการเขียน ซึ่งจิงๆแล้วก็เหมือนกันเขียน Query พวก select , insert , update , delete นั่นแหละครับ เพียงแต่ T-sql นั้นมี Control Structure พวก If , Case When , While และอื่นๆอยู่ด้วยนะครับ ทำให้เราสามารถเขียนโปรแกรมบนฐานข้อมูลได้สะดวกมากมายยิ่งขึ้น

ประเภทของ Store Procedure นั้นมี 3 ประเภทคือ
1. System Store Procedure คือ Store Procedure ของระบบ เช่น sp_helpdb ใช้เพื่อดูรายละเอียดของฐานข้อมูล เป็น Store procedure ที่มีอยู่แล้วในระบบ
2. Extended Store Procedure เช่น xp_cmdshell
3. User Store Procedure คือ Store procedure ที่เราสร้างขึ้นมาใช้งานเอง

ในที่นี้ผมจะขออ้างอิง ฐานข้อมูล Ms Sql Server นะครับเนื่องจากเป็นฐานข้อมูลที่ผมถนัด และที่เราจะอธิบายคือการ สร้าง User Store procedure 
ขั้นแรก เราต้องเตรียมฐานข้อมูลและสร้าง ตารางตัวอย่างพร้อมข้อมูลไว้สำหรับการทดสอบนะครับ ให้ใช้คำสั่ง Sql ด้านล่างนี้ในการสร้าง table และ ข้อมูลทดสอบ
สร้าง ตาราง และข้อมูลที่ใช้ในตัวอย่าง
  1. -- สร้าง table tbl_department ทดสอบ
  2. CREATE TABLE [dbo].[tbl_department](
  3. [dep_code] [varchar](2) COLLATE Thai_CI_AS NOT NULL,
  4. [dep_name] [varchar](50) COLLATE Thai_CI_AS NULL,
  5. CONSTRAINT [PK_tbl_department] PRIMARY KEY CLUSTERED 
  6. (
  7. [dep_code] ASC
  8. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  9. ) ON [PRIMARY]
  10. -- เพิ่มข้อมูลทดสอบใน tbl_department 
  11. insert into tbl_department values ('01','Account')
  12. insert into tbl_department values ('02','Marketing')
  13. insert into tbl_department values ('03','Information Technology')

  14. -- สร้าง table tbl_employee
  15. CREATE TABLE [dbo].[tbl_employee](
  16. [emp_code] [varchar](5) COLLATE Thai_CI_AS NOT NULL,
  17. [emp_name] [varchar](50) COLLATE Thai_CI_AS NULL,
  18. [emp_surname] [varchar](50) COLLATE Thai_CI_AS NULL,
  19. [emp_tel] [varchar](20) COLLATE Thai_CI_AS NULL,
  20. [emp_email] [varchar](60) COLLATE Thai_CI_AS NULL,
  21. [dep_code] [varchar](2) COLLATE Thai_CI_AS NULL,
  22. CONSTRAINT [PK_tbl_employee] PRIMARY KEY CLUSTERED 
  23. (
  24. [emp_code] ASC
  25. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  26. ) ON [PRIMARY]
  27. -- เพิ่มข้อมูลทดสอบใน tbl_employee
  28. insert into tbl_employee values ('E0001','สราวุธ','จงเจริญมั่นคง','081627xxxx','<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />nuijang20@hotmail.com','03')
  29. insert into tbl_employee values ('E0002','สุรเดช','ศรีเจริญ','081761xxxx','deth@hotmail.com','01')
  30. insert into tbl_employee values ('E0003','หิรัญ','ศรีสุพรรณ','081665xxxx','hirun@hotmail.com','02')
  31. insert into tbl_employee values ('E0004','อ่อนนุช','ชื่อซอยจ๊ะ','081555xxxx','onnuch@hotmail.com','02')
  32. insert into tbl_employee values ('E0005','บางนา','ชื่อถนนจ๊ะ','081222xxxx','bangna@hotmail.com','02')
คัดลอกไปที่คลิปบอร์ด
เมื่อเตรียมข้อมูลเรียบร้อยแล้วทีนี้ก็จะเข้าสู่การเขียน Store procedure ในตอนต่อไปนะครับ

การเขียน store procedure บน ms sql server ตอนที่ 2 รูปแบบต่างๆของ store procedure


บทความตอนที่ 2 นี้จะเป็นจะขอกล่าวถึงรูปแบบของ Store Procedure ในแบบต่างๆ ซึ่ง store procedure นั้นมีหลายๆรูปแบบ ผมจะขอจำแนกออกเป็นดังนี้ 
1. Stored procedures แบบทั่วๆไป2. Stored Procedures แบบรับ Parameter
     
2.1 Stored Procedures แบบกำหนดค่าเริ่มต้นให้ Parameter
     2.2 Stored Procedures แบบ Output Parameter
3. Stored Procedures แบบ Return ค่ากลับ

1. มาดูตัวอย่างเริ่มต้นการเขียน Store Procedure แบบทั่วๆไป กันก่อนนะครับ
  1. -- การสร้าง Store procedure แบบปกติ
  2. Create Procedure ชื่อ Store Procedure 
  3. As
  4. BEGIN
  5. คำสั่ง Sql Statement
  6. END
คัดลอกไปที่คลิปบอร์ด
ตัวอย่างเช่น
  1. Create Procedure sp_fullemployee
  2. As
  3. BEGIN

  4. select 
  5. d.dep_code,d.dep_name,e.emp_code,e.emp_name,e.emp_surname,e.emp_tel,e.emp_email
  6. from tbl_department d left join tbl_employee e on e.dep_code=d.dep_code
  7. END

  8. -- การเรียกใช้ Store ที่สร้างขึ้นนั้นเราจะใช้คำสั่ง execute ดังนี้
  9. EXEC fullemployee

  10. ผลลัพธ์ที่ได้คือ
  11. 01 Account E0002 สุรเดช ศรีเจริญ 081761xxxx deth@hotmail.com
  12. 02 Marketing E0003 หิรัญ ศรีสุพรรณ 081665xxxx hirun@hotmail.com
  13. 02 Marketing E0004 อ่อนนุช ชื่อซอยจ๊ะ 081555xxxx onnuch@hotmail.com
  14. 02 Marketing E0005 บางนา ชื่อถนนจ๊ะ 081222xxxx bangna@hotmail.com
  15. 03 Information Technology E0001 สราวุธ จงเจริญมั่นคง 081627xxxx nuijang20@hotmail.com
คัดลอกไปที่คลิปบอร์ด

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


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

  1. -- การสร้าง Stored Procedure แบบรับค่าParameter
  2. Create Procedure sp_fullemployee_by_emp_code (
  3.    @parameter1 datatype ,
  4.    @parameter2 datatype ,
  5.    @parameter3 datatype ,
  6.    …
  7. )
  8. As
  9. BEGIN
  10. คำสั่ง Sql Statement
  11. END
คัดลอกไปที่คลิปบอร์ด
ตัวอย่างการสร้าง Store procedure แบบมี parameter นะครับ จะเป็นการสร้าง store procedure สำหรับ แสดงข้อมูล employee แบบที่มีการใส่เงื่อนไข ตาม emp_code ดังโคดด้านล่าง

  1. create procedure sp_fullemployee_by_emp_code (
  2.                 @emp_code varchar(5)
  3. AS
  4. BEGIN
  5. select 
  6. d.dep_code,d.dep_name,e.emp_code,e.emp_name,e.emp_surname,e.emp_tel,e.emp_email
  7. from tbl_department d left join tbl_employee e on e.dep_code=d.dep_code
  8. where e.emp_code=@emp_code
  9. END
  10. -- การเรียกใช้งาน Stored procedures แบบรับค่า parameter 
  11. exec sp_fullemployee_by_emp_code 'E0002'
  12. -- หรือ ส่งค่าให้กับ parameter ที่ต้องการ โดยการกำหนดชื่อดังเช่นตัวอย่างด้านล่างก็ได้ ตัวอย่างนี้จะใช้บ่อยๆ ในกรณี สร้าง Store procedure แบบกำหนดค่าเริ่มต้นให้กับ parameter 
  13. exec sp_fullemployee_by_emp_code @emp_code='E0002'
คัดลอกไปที่คลิปบอร์ด
2.1 Stored Procedures แบบกำหนดค่าเริ่มต้นให้ Parameter ในการสร้าง Store procedure แบบนี้จะเอาไว้สำหรับ Store procedure ที่มีการรับหลายๆ parameter และมี บาง parameter ที่ค่าข้อมูลเหมือนๆกัน จึงกำหนดเป็นค่าเริ่มต้นได้ จะทำให้เวลาเรียกใช้งาน จะได้ง่ายขึ้น ไม่ต้องใส่ parameter ให้ครบทุกตัว

  1. -- การสร้าง Stored Procedure แบบกำหนดค่าเริ่มต้นให้ Parameter 
  2. Create Procedure sp_fullemployee_by_emp_code (
  3.    @parameter1 datatype ,
  4.    @parameter2 datatype=Default Value,
  5.    @parameter3 datatype ,
  6.    …
  7. )
  8. As
  9. BEGIN
  10. คำสั่ง Sql Statement
  11. END
คัดลอกไปที่คลิปบอร์ด
ตัวอย่างเช่น การสร้าง Store procedure สำหรับเพิ่มข้อมูลพนักงานใหม่ (จากตัวอย่าง ผมจะแสดงให้ดูถึงข้อมูลจึงใช้คำสั่ง select @parameter นะครับ หากจะใช้จริง ก็เขียน ตรง Sql statement เป็น insert ไปแทน

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

  16. หรือ หากต้องการเปลี่ยนค่า ให้กับค่าเริ่มต้น บาง parameter เราก็สามารถทำได้เช่น 
  17. exec sp_insert_employee 'E0006','ทดสอบ','นามสกุล' ,@dep_code='02'
  18. จากตัวอย่าง ผมต้องการเปลี่ยน @dep_code เท่านั้นก็สามารถทำได้ โดยการระบุชื่อ Parameter ที่ต้องการ แล้วใส่ค่า value เข้าไปเท่านั้นเองครับ
คัดลอกไปที่คลิปบอร์ด
2.2 Stored Procedures แบบ Output Parameter ใช้ในกรณีที่เราต้องการ ให้ Store procedure มีการส่งค่าบางอย่างกลับมาให้เรานะครับ
ซึ่งการใช้วิธีนี้ เราสามารถ สร้างตัวแปร output ส่งค่ากลับมาได้หลายตัวแปร จะต่างจาก การสร้าง Store procedure แบบ return ซึ่ง ส่งค่ากลับมาได้ ตัวเดียวนะครับ

  1. -- การสร้าง Stored Procedure แบบ Output Parameter 
  2. Create Procedure sp_fullemployee_by_emp_code (
  3.    @parameter1 datatype ,
  4.    @parameter2 datatype=Default Value,
  5.    @parameter3 datatype output,
  6.    …
  7. )
  8. As
  9. BEGIN
  10. คำสั่ง Sql Statement
  11. END
คัดลอกไปที่คลิปบอร์ด
ตัวอย่างเช่น การสร้าง Store procedure สำหรับค้นหาข้อมูลพนักงาน ที่ต้องการค่า output คือจำนวนที่ค้นหาพบ ดังนั้นเราจึงต้องให้มี Output parameter เป็นจำนวนที่ค้นหาพบ จึงเขียน Store procedure ได้ดังนี้

  1. create procedure sp_search_employee(
  2.                 @txtsearch varchar(50),
  3.                 @ItemCount int output 
  4. )
  5. AS
  6. BEGIN
  7. -- query ข้อมูล
  8. select * from tbl_employee
  9. where (emp_code + ' ' + emp_name + ' ' + emp_surname) like '%'+@txtsearch+'%'
  10. -- นับจำนวน
  11. set @ItemCount=(select count(*) from tbl_employee
  12.                              where (emp_code + ' ' + emp_name + ' ' + emp_surname) like '%'+@txtsearch+'%'
  13.                                                                 )
  14. 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 ได้นะครับ 

การเขียน store procedure บน ms sql server ตอนที่ 4 การเขียน Store procedure แบบ Return ค่ากลับ


3. Stored Procedures แบบ Return ค่ากลับ 
     การสร้าง store procedure แบบ return ค่ากลับมานั้น ก็สามารถทำได้ ซึ่งปกติแล้วการ return store procedure นั้นเราจะใช้เพื่อตรวจสอบสถานะของ Store procedure เท่านั้น เนื่องจากมันคืนค่ากลับมาเป็น interger และ จะ return กลับมาได้เพียง 1 ค่า (ยกเว้น เราจะใช้วิธี การ ใช้ร่วมกับ output parameter)
  1. -- การสร้าง Stored Procedure return ค่ากลับ

  2. Create Procedure sp_fullemployee_by_emp_code (
  3. @parameter1 datatype ,
  4. @parameter2 datatype=Default Value,
  5. @parameter3 datatype output,
  6. )
  7. As
  8. BEGIN
  9. Declare @num int
  10. @num=คำสั่ง Sql Statement
  11. RETURN @num
  12. END
คัดลอกไปที่คลิปบอร์ด
ตัวอย่างเช่น 
     การสร้าง Store procedure เพื่อ Update ข้อมูล Employee จะมีการ Return ค่า @@error กลับมาเพื่อให้ทราบว่าสถานะของ Store procedure นั้นทำงานเป็นอย่างไร 
(ค่า 0 ไม่พบ error แต่หากพบ error เราสามารถใช้ View sys.messages เพื่อหาดูได้ว่า เกิดจาก error อะไรเช่น 
select * from sys.messages where message_id=8134 and language_id=1033
ขออธิบาย ซักนิด message_id คือ หมายเลข error id ที่ได้จาก @@error และ language_id คือ ภาษา 1033 เป็น us_english)
  1. create procedure sp_update_employee (
  2. @emp_code varchar(5) ,
  3. @emp_name varchar(50) ,
  4. @emp_surname varchar(50) ,
  5. @emp_tel varchar(20)='-' , -- กำหนดค่าเริ่มต้นเป็น -
  6. @emp_email varchar(60)='-' , -- กำหนดค่าเริ่มต้นเป็น -
  7. @dep_code varchar(2)='01' -- กำหนดค่าเริ่มต้นเป็นฝ่าย Account
  8. )
  9. AS
  10. BEGIN
  11. Update tbl_employee set 
  12.            emp_name = @emp_name ,
  13.            emp_surname=@emp_surname ,
  14.            emp_tel = @emp_tel ,
  15.            emp_email=@emp_email ,
  16.            dep_code=@dep_code
  17. Where emp_code=@emp_code

  18. Return @@error

  19. END
คัดลอกไปที่คลิปบอร์ด
-- การเรียกใช้งาน การ update ข้อมูล employee
  1. Declare @err int
  2. exec @err=sp_update_employee 'E0005','นาย บางนา','นามสกุล ถนน' ,@dep_code='03'

  3. select @err
คัดลอกไปที่คลิปบอร์ด
ผลลัพท์ที่ได้จะไม่พบ Error เนื่องจาก การประมวลผลถูกต้อง

การเขียน store procedure บน ms sql server ตอนที่ 5 การสร้าง Store procedure แบบ รับ เงื่อนไข อิสระ


ก็มาถึงบทความเรื่อง store procedure ในตอนที่ 5 แล้วนะครับ 
     ในเนื้อหาที่เป็นพื้นฐานจริงๆ นั้นจบไปตั้งแต่ บทความตอนที่ 4 แล้วนะครับ เพราะ วิธีการเขียน t-sql control structure พวก if , while ที่จำเป็นต้องใช้ในการสร้าง store procedure แบบ Advance ผมจะค่อยๆ เขียนมาเพิ่มทีหลังครับ 

ในตอนนี้ที่ 5 นี้ก็จะเป็นการประยุกต์สร้าง store procedure ให้ยืดหยุ่น เพิ่มขึ้นเท่านั้นนะครับเป็นเทคนิคเล็กๆ เฉยๆ เกี่ยวกับการใส่เงื่อนไขให้ store procedure นะครับ ลองดูครับ


ตอนพิเศษเรื่อง Store procedure การสร้าง Store procedure แบบ รับ เงื่อนไข อิสระ

จาก Store Procedure ที่ผ่านมา หากเราต้องการ ใช้เงื่อนไขที่อยาก ใส่เองแบบอิสระ จะทำไม่ได้ เนื่องจาก เรา ไม่สามารถใส่ชุดคำสั่ง Where ตามที่เราต้องการได้ เพื่อให้ Store procedure ที่เราสร้างยืดหยุ่น ขึ้นไปอีก จึงเป็นที่มาของ บทความตอนนี้นะครับ
ตัวอย่างการสร้าง Store Procedure สำหรับ กำหนดเงื่อนไขอิสระ

  1. create procedure sp_select_filter_employee(
  2.                 @condition varchar(500)='',
  3.                 @order varchar(100)='emp_code'
  4. )
  5. AS
  6. BEGIN
  7.                 EXEC(
  8.                                 'select * from tbl_employee ' +
  9.                                 @condition + ' ' + 
  10.                                 'order by '+ @order
  11.                 )
  12. END
คัดลอกไปที่คลิปบอร์ด
จะเห็นว่า มีการสร้าง parameter @condition ขนาด 500 ตัวอักษร เพื่อให้เรา สามารถใส่เงื่อนไข Where ได้อย่างอิสระ จริงๆ และ มี parameter @order สำหรับ sort ข้อมูลเช่นกัน

มาดูตัวอย่างการใช้งานบ้างนะครับ
  1. Declare @condition varchar(500),@order varchar(100)
  2. set @condition='where (emp_code + '' '' + emp_name + '' '' + emp_surname) like ''%เจริญ%'''
  3. set @order='emp_name'
  4. exec sp_select_filter_employee @condition,@order
คัดลอกไปที่คลิปบอร์ด
หมายเหตุ จากตัวแปร @condition ที่เรากำหนด สำหรับ field ทีเป็น string เราจะต้องใส่ ' ครอบ ตัวอักษร เพื่อเปรียบเทียบข้อมูล แต่ในที่นี้ ให้เราเพิ่มเป็น '' 2 ตัว เช่น 
set @condition='where emp_name=''สราวุธ'''