Stored Procedures

Khi dùng Query Analyzer chúng ta có thể đặt tên và save các nhóm câu lệnh SQL vào một file dưới dạng script để có thể sử dụng trở lại sau này. Tuy nhiên thay vì save vào text file ta có thể save vào trong SQL Server dưới dạng Stored Procedure. Stored Procedure là một nhóm câu lệnh Transact-SQL đã được compiled (biên dịch) và chứa trong SQL Server dưới một tên nào đó.

Ưu Ðiểm Của Stored Procedure
Stored Procedure có một số ưu điểm chính như sau:
·                                 Performance : Khi thực thi một câu lệnh SQL thì SQL Server phải kiểm tra permission xem user gởi câu lệnh đó có được phép thực hiện câu lệnh hay không đồng thời kiểm tra cú pháp rồi mới tạo ra một execute plan và thực thi. Nếu có nhiều câu lệnh như vậy gởi qua network có thể làm giảm đi tốc độ làm việc của server. SQL Server sẽ làm việc hiệu quả hơn nếu dùng stored procedure vì người gởi chỉ gởi một câu lệnh đơn và SQL Server chỉ kiểm tra một lần sau đó tạo ra một execute plan và thực thi. Nếu stored procedure được gọi nhiều lần thì execute plan có thể được sử dụng lại nên sẽ làm việc nhanh hơn. Ngoài ra cú pháp của các câu lệnh SQL đã được SQL Sever kiểm tra trước khi save nên nó không cần kiểm lại khi thực thi.
·                                 Programming Framework : Một khi stored procedure được tạo ra nó có thể được sử dụng lại. Cũng giống như các ngôn ngữ lập trình khác stored procedure cho phép ta đưa vào các input parameters (tham số) và trả về các output parameters đồng thời nó cũng có khả năng gọi các stored procedure khác.
·                                 Security : Truy xuất dữ liệu trực tiếp của một user nào đó vào một số tables, ta có thể viết một stored procedure để truy xuất dữ liệu và chỉ cho phép user đó được sử dụng stored procedure đã viết sẵn. Stored procedure có thể được encrypt (mã hóa) để tăng cường tính bảo mật.
Các Loại Stored Procedure
Stored procedure có thể được chia thành 5 nhóm như sau:
1.                              System Stored Prcedure : Là những stored procedure chứa trong Master database và thường bắt đầu bằng tiếp đầu ngữ sp_ . Các stored procedure này thuộc loại built-in và chủ yếu dùng trong việc quản lý database (administration) và security. Ví dụ bạn có thể kiểm tra tất cả các processes đang được sử dụng bởi user DomainName\Administrators bạn có thể dùng sp_who @loginame='DomainName\Administrators' . Có hàng trăm system stored procedure trong SQL Server. Bạn có thể xem chi tiết trong SQL Server Books Online.
2.                              Local Stored Procedure : Ðây là loại thường dùng nhất. Chúng được chứa trong user database và thường được viết để thực hiện một công việc nào đó. Thông thường người ta nói đến stored procedure là nói đến loại này. Local stored procedure thường được viết bởi DBA hoặc programmer. Chúng ta sẽ bàn về cách tạo stored prcedure loại này trong phần kế tiếp.
3.                              Temporary Stored Procedure : Là những stored procedure tương tự như local stored procedure nhưng chỉ tồn tại cho đến khi connection đã tạo ra chúng bị đóng lại hoặc SQL Server shutdown. Các stored procedure này được tạo ra trên TempDB của SQL Server nên chúng sẽ bị delete khi connection tạo ra chúng bị cắt đứt hay khi SQL Server down. Temporary stored procedure được chia làm 3 loại : local (bắt đầu bằng #), global (bắt đầu bằng ##) và stored procedure được tạo ra trực tiếp trên TempDB. Loại local chỉ được sử dụng bởi connection đã tạo ra chúng và bị xóa khi disconnect, còn loại global có thể được sử dụng bởi bất kỳ connection nào. Permission cho loại global là dành cho mọi người (public) và không thể thay đổi. Loại stored procedure được tạo trực tiếp trên TempDB khác với 2 loại trên ở chỗ ta có thể set permission, chúng tồntại kể cả sau khi connection tạo ra chúng bị cắt đứt và chỉ biến mất khi SQL Server shut down.
4.                              Extended Stored Procedure : Ðây là một loại stored procedure sử dụng một chương trình ngoại vi (external program) vốn được compiled thành một DLL để mở rộng chức năng hoạt động của SQL Server. Loại này thường bắt đầu bằng tiếp đầu ngữ xp_ .Ví dụ, xp_sendmail dùng đề gởi mail cho một người nào đó hay xp_cmdshell dùng để chạy một DOS command... Ví dụ  xp_cmdshell 'dir c:\' . Nhiều loại extend stored procedure được xem như system stored procedure và ngược lại.
5.                              Remote Stored Procedure : Những stored procedure gọi stored procedure ở server khác.
Viết Stored Procedure
Ta cũng có thể dùng các lệnh CREATE, ALTER, DROP để tạo mới, thay đổi hay xóa bỏ một stored procedure.
Ví dụ: Tạo một stored procedure dùng Enterprise Manager click lên trên Stored Procedure -> New Stored Procedure....
Ta sẽ tạo ra một stored procedure để insert một new order vào Orders table trong Practice DB. Ðể insert một order vào database ta cần đưa vào một số input như OrderID, ProductName và CustomerName. Sau đó ta trả về kết quả cho biết việc insert đó có thành công hay không. Result = 0 là insert thành công.
             CREATE PROCEDURE AddNew
             @OrderID smallint,
             @ProductName varchar(50),
             @CustomerName varchar(50),
             @Result smallint=1 Output
             AS

             DECLARE @CustomerID smallint
             BEGIN TRANSACTION
             If not Exists(SELECT CustomerID FROM Customers WHERE [Name]=@CustomerName)
             --This is a new customer. Insert this customer to the database
                             BEGIN
                                            SET @CustomerID= (SELECT Max(CustomerID) FROM Customers)
                                            SET @CustomerID=@CustomerID+1
                                            INSERT INTO Customers VALUES(@CustomerID,@CustomerName)
                                            If Exists(SELECT OrderID FROM [Orders] WHERE OrderID=@OrderID)
                                            --This order exists and could not be added any more so Roll back
                                                           BEGIN
                                                                          SELECT @Result=1
                                                                          ROLLBACK TRANSACTION
                                                           END
                                            Else
                                            --This is a new order insert it now
                                                           BEGIN
                                                                          INSERT INTO [Orders](OrderID,ProductName,CustomerID) VALUES(@OrderID,@ProductName,@CustomerID)
                                                                          SELECT @Result=0
                                                                          COMMIT TRANSACTION
                                                           END
                             END
             Else
             --The customer exists in DB go ahead and insert the order
                             BEGIN
                                            If Exists(SELECT OrderID FROM [Orders] WHERE OrderID=@OrderID)
                                            --This order exists and could not be added any more so Roll back
                                                           BEGIN
                                                                          SELECT @Result=1
                                                                          ROLLBACK TRANSACTION
                                                           END
                                            Else
                                            --This is a new order insert it now
                                                           BEGIN
                                                                          INSERT INTO [Orders](OrderID,ProductName,CustomerID) VALUES(@OrderID,@ProductName,@CustomerID)
                                                                          SELECT @Result=0
                                                                          COMMIT TRANSACTION
                                                           END
                             END
             Print @Result
             Return
Dùng lệnh CREATE PROCEDURE tạo một stored procedure (nếu là temporary stored procedure thì thêm dấu # trước tên của procedure. Nếu muốn encrypt thì dùng WITH ENCRYPTION trước chữ AS) và các input hoặc ouput parameters. Nếu là output thì thêm keyword OUTPUT đằng sau parameter. Ta có thể cho giá trị default cùng lúc với khai báo data type của parameter. Kể từ sau chữ AS là phần body của stored procedure.
Trong ví dụ ở trên trước hết ta khai báo một biến @CustomerID sau đó bắt đầu một transaction bằng BEGIN TRANSACTION (toàn bộ công việc insert này được thực hiện trong một Transaction nghĩa là hoặc là insert thành công hoặc là không làm gì cả- all or nothing). Trước hết ta kiểm tra xem người khách hàng là người mới hay cũ. Nếu là người mới thì ta "tiện tay" insert vào Customers table luôn còn nếu không thì chỉ insert vào Orders table mà thôi. Nếu là người customer mới ta lấy CustomerID lớn nhất từ Customers table bỏ vào biến @CustomerID và sau đó tăng lên một đơn vị dùng cho việc Insert Customer ở dòng kế tiếp.
Sau khi insert người customer mới ta tiếp tục công việc bằng việc kiểm tra xem Order muốn insert có tồn tại chưa (vì nếu order đã tồn tại thì khi insert SQL Server sẽ báo lỗi do OrderID là Primary key). Nếu như order trên vì lý do nào đó đã có trong DB thì ta roll back và trả kết quả =1 còn nếu không thì ta insert một order mới vào và commit transaction với kết quả trả về =0.
Các ví Dụ tham khảo nữa nhe:
CREATE procedure XOA_KHACHHANG
          (
          @MSKH char(10)
          ) as
Begin
          if (exists(select *from KHACHHANG where MSKH=@MSKH))
          begin
          if(not exists(select *from HOADON where MSKH=@MSKH))
            begin
            delete KHACHHANG where MSKH=@MSKH
              if @@rowcount=0
                                  raiserror ('Xoa khach hang khong thanh cong',1,1)
              else
                                  raiserror ('Xoa khach hang thanh cong',1,1)        
             end
                      else
              raiserror ('Khong the xoa khach hang nay ',1,1)
            end
            else
            raiserror ('Khavh hang nay khong ton tan',1,1)

            end

CREATE procedure SUA_KHACHHANG
          (
          @MSKH char(10),
          @TENKH  varchar(50),
          @DIACHI  varchar(100),
          @PHAI     char(5),
          @DIENTHOAI varchar(15)
          ) as
Begin
          if (exists(select *from KHACHHANG where MSKH=@MSKH))
begin
if isnumeric(@DIENTHOAI)=1 or @DIENTHOAI=''      
begin
          if @DIENTHOAI='' or DATALENGTH(@DIENTHOAI)=6 or DATALENGTH(@DIENTHOAI)=7 or DATALENGTH(@DIENTHOAI)=8 or DATALENGTH(@DIENTHOAI)=9 or DATALENGTH(@DIENTHOAI)=10 or DATALENGTH(@DIENTHOAI)=11
          begin
           if @DIENTHOAI=''
           set @DIENTHOAI=null
           update KHACHHANG
           set TENKH=@TENKH,DIACHI=@DIACHI,PHAI=@PHAI,DIENTHOAI=@DIENTHOAI where MSKH=@MSKH
            if @@rowcount=0
                      raiserror ('Sua khach hang khong thanh cong',2,2)
            else
                      raiserror ('Sua khach hang thanh cong',2,2)
          end
          else
            raiserror ('So dien thoai ko hop le',3,1)
end
else
raiserror ('So dien thoai ko hop le',3,1)
end
else
raiserror ('khach hang nay khong ton tai',4,2)
end