Tag Archive: SQL


Câu lệnh Set RowCount trong SQL Server

Hôm nay tôi chia sẻ chút kiến thức về câu lệnh Set RowCount trong SQL Server.

Ta xem câu lệnh sau:

1
2
3
4
5
6
USE AdventureWorks;
GO
SELECT count(*) AS Count
FROM Production.ProductInventory
WHERE Quantity < 300;
GO

Đó là 1 câu lệnh hoàn toàn đơn giản, kết quả của nó là:

1
2
3
4
5
Count
 -----------
 537
(1 row(s) affected)

Bây giờ, dùng câu lệnh RowCount để update số dòng theo điều kiện của câu select ở trên, ta thấy chỉ có 4 dòng bị tác động

1
2
3
4
5
6
SET ROWCOUNT 4;
UPDATE Production.ProductInventory
SET Quantity = 400
WHERE Quantity < 300;
GO
(4 row(s) affected)

Câu lệnh RowCount có tác dụng dừng xử lý cho các câu lệnh Insert, Select, Update theo số dòng chỉ định, như ví dụ ở trên ta yêu cầu chỉ update 4 dòng, mặc dù điều kiện Where trả về nhiều kết quả hơn.

1 vấn đề đặt ra là câu lệnh Set RowCount có gì khác so với câu lệnh Select top?

Câu trả lời là việc xử lý bị dừng ngay lập tức khi số dòng trả về bằng số dòng được định nghĩa trong RowCount. Còn câu lệnh Select top thì nó vẫn trả về toàn bộ số dòng bên trong SQL Server, rồi sau đó mới lấy top n record trả về.

Set RowCount vẫn được SQL Server 2012 hỗ trợ, tuy nhiên nó sẽ bị loại bỏ ở phiên bản tiếp theo của SQL Server.

Tham khảo: http://msdn.microsoft.com/en-us/library/ms188774.aspx

Quang Vinh
mai.quangvinh@sqlvn.com

Tổng hợp các thủ thuật lập trình SQL server

1. Sử dụng hàm Isnull

Giá trị là null trong SQL đôi khi khiến kết quả tính toán của ta không thể thực hiện được. Để đảm bảo giá trị truy vấn hoặc truyền vào biến luôn có giá trị (Không phải null) bạn sử dụng hàm isnull như ví dụ:

DECLARE @count int
SELECT @count=isnull(Count(*),0) FROM TB_News tn WHERE tn.idNews=10

Ở ví dụ này nếu bảng TB_News của bạn không có idNews=10 thì sẽ trả về null nhưng nếu bạn sử dụng isnull thì biến @count của bạn sẽ là 0. Nếu không sử dụng isnull bạn thường phải làm như sau:

	
DECLARE @count int
ELECT @count =Count(*) FROM TB_News tn WHERE tn.idNews=10
IF @count IS NULL SET @count =0

Bạn có thể so sánh 2 cách và lựa chọn

 

2. Cập nhật số lần xem bài viết

Khi người dùng truy cập website của bạn và chọn một bài viết để đọc bạn muốn theo dõi số lượt bài viết đó được xem bao nhiêu lần thì trong bảng TB_News (lưu các bài viết của bạn) cần có thêm trường NumRead (int), Khi bài viết được xem bạn cần viết thủ tục như sau để vừa truy vấn vừa cập nhật số lần xem

CREATE PROCEDURE sTB_News_SelectByID
@idNews int
AS
BEGIN
	--Cập nhật số lần xem trước khi hiển thị
	DECLARE @numread int
	SELECT @numread=isnull(tn.NumRead,0) FROM TB_News tn WHERE tn.idNews=@idNews
	UPDATE TB_News SET NumRead = @numread+1 WHERE idNews=@idNews
	-- Truy vấn hiển thị
	SELECT tn.idNews, tn.CateID, tn.LanguageCode, tn.UserName, tn.Title,
	       tn.Description, tn.Content, tn.Keyword, tn.Author, tn.CreateDate,tn.NumRead,
	       tn.Status
	  FROM TB_News tn WHERE tn.idNews=@idNews
END

Chú ý là các trường và tên bảng trong CSDL của bạn sẽ khác, Và ở ví dụ này mình sẽ cập nhật số lần xem trước khi truy vấn dữ liệu

3. Hàm Trim trong SQL

Trong SQL không có hàm trim nhưng lại có các hàm RTRIM và LTRIM vậy ta có thể kết hợp hai hàm này để có hàm Trim

Create Function fTrim
(
	@str nvarchar(500)
)
Returns nvarchar(500)
As
BEGIN
	Return RTRIM(LTRIM(@str))
End


Download:
http://www.fshare.vn/file/TK6NPP6YJT

Password:
sinhvienit.net

Nguồn: SINHVIENIT.net

Hướng dẫn cài đặt hệ quản trị cơ sở dữ liệu Microsoft SQL Server 2005 (Express Edition)

SQL Server 2005 có nhiều phiên bản khác nhau, trong đó bản Express là bản thấp nhất, được Microsoft cung cấp miễn phí cho người dùng với mục đích học tập và ứng dụng vào những ứng dụng nhỏ, không yêu cầu cao về các tính năng khác ngoài việc lưu trữ và xử lý đơn giản.

Yêu cầu về phần cứng và hệ điều hành sử dụng

  • Hệ điều hành tối thiểu: Windows 2000 Service Pack 4; Windows Server 2003 Service Pack 1; Windows XP Service Pack 2
  • Phần cứng:
    • Máy tính chip Pentium III 600 MHz trở lên (Cấu hình đề nghị: Chip 1 GHz hoặc cao hơn.)
    • Tối thiểu 192 MB RAM (Cấu hình đề nghị: 512 MB RAM.)
    • Ổ cứng còn trống tối thiểu 525 MB

Bộ cài đặt:

Sau khi download về, hãy lưu vào một thư mục nào đó để bắt đầu tiến hành cài đặt

Trong hướng dẫn dưới đây, các thành phần trên có tên file cài đặt lần lượt là

  1. WindowsInstaller-KB893803-v2-x86.exe: Windows Installer 3.1
  2. dotnetfx.exe: Microsoft .Net Framework 2.0
  3. SQLEXPR.EXE: SQL Server 2005 Express
  4. SQLServer2005_SSMSEE.msi: Công cụ quản lý SQL Server Management Studio Express

Bước 1: Cài Windows Installer 3.1

Nhấn vào file WindowsInstaller-KB893803-v2-x86.exe

Nhấn Next

Chọn I agree, nhấn Next

Click Finish


Bước 2: Cài Framework 2.0

File cài: dotnetfx.exe

Nhấn Finish

Máy sẽ khởi động lại

Sau đó tiếp tục bước 3
 

Bước 3: Cài Microsoft SQL Server 2005

Nhấn vào file SQLEXPR.EXE

Trên màn hình tiếp theo, đánh dấu vào mục: “I accept the licensing terms and conditions”, nhấn Next

Trên màn hình tiếp theo, nhấn Install

Nhấn Next

Tiếp tục nhấn Next

Trên màn hình tiếp theo, bỏ dấu check mở mục “Hide advanced configuration options”, nhấn Next.

Nhấn Next trên màn hình tiếp theo

Khai báo Instance name, chọn Default Instance (Server Name sẽ trùng với tên máy)

Nhấn Next

Nhấn Next trên cửa sổ tiếp theo

Trên cửa sổ tiếp theo, chọn chế độ chứng thực user

  • Windows Authentication Mode: Sử dụng chứng thực của Windows
  • Mixed Mode: Kết hợp cả chứng thực của Windows và chứng thực của SQL Server

Ở đây có thể để mặc định: “Windows Authentication Mode”

Nhấn Next

Nhấn Next

Nhấn Next

Nhấn Next

Click Install để bắt đầu cài đặt

Quá trình cài đặt có thể mất 5 -> 10 phút

Khi màn hình báo cài đặt thành công như hình dưới, click Next

Tiếp tục click Finish

Bước 4: Cài Microsoft SQL Server Management Studio Express:

File cài đặt SQLServer2005_SSMSEE.msi

Nhấn Next

Click chọn “I accept the terms in the license agreement”, nhấn Next

Gõ tên người dùng, đơn vị sử dụng, sau đó nhấn Next

Màn hình tiếp theo, nhấn Next

Click Install

Đợi trong vài phút

Nhấn Finish

Đến đây chúng ta đã cài đặt xong SQL Server 2005 Express và các công cụ để quản lý database của nó. Từ đây bạn có thể bắt đầu sử dụng các tính năng quản lý của hệ quản trị cơ sở dữ liệu này rồi. Cách thức sử dụng các tính năng phổ biến cũng như một vài tính năng hay của nó mình sẽ đề cập đến trong các bài sau.

—– Cập nhật—

Cách ở trên mình hướng dẫn là đã đầy đủ các bước để cài SQL Server 2005 Express rồi. Tuy nhiên do mình chưa giải thích kỹ hơn nữa ý nghĩa của các bước, các lựa chọn nên chắc là trong quá trình cài bạn có sai sót gì đấy rồi.
Trường hợp bạn Attach database mà bị lỗi thì có thể do nhiều nguyên nhân, trong đó có thể là do database bị lỗi, hoặc nhiều nguyên nhân khác.
Một điểm cần lưu ý khi cài đặt SQL Server có thể dẫn đến Attach database bị lỗi, đó là bạn đã chọn sai collation, xem hình này

Bước chọn collation bạn nên để mặc định Windows 1252 character set, nếu bạn chọn sai, chỉ có cách cài lại SQL Server.
Vui lòng post cụ thể lỗi để mọi người có thể giúp bạn.

 

Trong bài trước mình đã giới thiệu về Stored Procedure, Trong bài này chúng ta sẽ tìm hiểu về  Function và Trigger – một phần cũng không kém phần quan trong trong lập trình với cơ sở dữ liệu

1. Hàm – Functions

Cũng giống như Stored Procedure Hàm là một đối tượng trong cơ sở dữ liệu bao gồm một tập nhiều câu lệnh SQL được nhóm lại với nhau thành một nhóm. Điểm khác biệt giữa hàm và thủ tục là hàm trả về một giá trị thông qua tên hàm. Điều này cho phép ta sử dụng hàm như là một thành phần của một biểu thức chẳng hạn như trong các câu lệnh truy vấn hay các câu lệnh thực hiện cập nhật dữ liệu

Trong SQL có rất nhiều các hàm được định nghĩa sẵn (Được chia theo nhóm – Trong 1 Database bạn chọn Programmability/Functions/System Functions) như các hàm về chuỗi (String Functions), các hàm về ngày tháng (Date and Time Functions), Các hàm toán học (Mathematical Function), … Ngoài những hàm do hệ quản trị cơ sở dữ liệu cung cấp sẵn, bạn có thể tự xây dựng các hàm nhằm phục vụ cho mục đích riêng của mình – Các hàm do người dùng định nghĩa. Các hàm do người dùng định nghĩa thường có 2 loại: Loại 1 là Hàm với giá trị trả về là “dữ liệu kiểu bảng” – Table-valued Functions; Loại 2 là Hàm với giá trị trả về là một giá trị – Scalar-valued Functions và các hàm này cũng sẽ được Hệ quản trị phân thành 2 nhóm.

Các hàm sẵn có của SQL bạn tự tìm hiểu và sử dụng, trong bài viết này mình giới thiệu qua về những hàm “Do người dùng định nghĩa”.

Cú pháp của hàm như sau:

CREATE FUNCTION Ten_Ham ( [Danh_Sach_Cac_Tham_So] )
RETURNS Kieu_Du_Lieu_Tra_Ve_Cua_Ham
AS
BEGIN
Cac_Cau_Lenh_Cua_Ham
END

 Ten_Ham: Tên của hàm cần tạo. Tên phải tuân theo qui tắc định danh và không trùng với tên của các hàm hệ thống có sắn.
– Danh_Sach_Cac_Tham_So: Các tham số của hàm được khai báo ngay sau tên hàm và được bao bởi cặp dấu (), Danh sách các tham số này có thể không có – trường hợp này thì sau tên hàm bạn cần có cặp dấu (). Nếu hàm có nhiều tham số thì các khai báo phân cách nhau bởi dấu phẩy và phải bao hồm 2 phần: Tên tham số được bắt đầu bởi dấu @, Kiểu dữ liệu của tham số
– Cac_Cau_Lenh_Cua_Ham: Tập hợp các câu lệnh sử dụng trong nội dung hàm để thực hiện các yêu cầu của hàm.

Giờ chúng ta sẽ xem các ví dụ cụ thể để hiểu hơn về hàm nhé:
Ví dụ 1: Mình sẽ lấy một ví dụ thật đơn giản là hàm không có Danh_Sach_Cac_Tham_So – trả về giá trị là năm hiện hành (Theo giờ hệ thống trên máy Database server):

CREATE  FUNCTION dbo.fuGetCurrYear ()
RETURNS int
AS
BEGIN
RETURN   YEAR(getdate())
END
Xem ví dụ trên bạn sẽ thấy nó rất đơn giản nhưng qua đây bạn cũng đã biết được việc viết hàm trong SQL như thế nào.

Ví dụ 2: Tiếp theo mình sẽ viết một ví dụ nữa để bạn hiểu và có thể viết cho mình các hàm tự định nghĩa:
Ví dụ này sẽ có 2 tham sô trong Danh_Sach_Cac_Tham_So. Hàm sẽ trả về số ngày của tháng, năm do bạn truyền vào; Bạn biết khi lập trình với Pascal bạn đã quen với bài toán tính số ngày của thàng – Với năm nhuận thì tháng 2 có 29 ngày, các năm khác có 28 ngày. (Qua hàm này bạn cũng sẽ hiểu hơn về điều khiển IF (Xem thêm bài viết Kỹ thuật phân trang bằng Store Procedure để hiểu hơn về cách sử dụng IF trong SQL) và sử dụng Case – (Xem bài viết về Hàm Case trong SQL để hiểu hơn về Case)

 CREATE  FUNCTION dbo.fuDaysInMonth (
 @Thang Int,
 @Nam  Int
) 
RETURNS int
AS 
BEGIN  
 DECLARE @Ngay Int
 IF @Thang = 2
  BEGIN
   IF ((@Nam % 4 = 0 AND @Nam % 100 <> 0)
    OR (@Nam % 400 = 0))
    SET @Ngay = 29
   ELSE
    SET @Ngay = 28
  END
 ELSE
  SELECT @Ngay = 
   CASE @Thang
    WHEN 1 THEN 31
    WHEN 3 THEN 31
    WHEN 5 THEN 31
    WHEN 7 THEN 31
    WHEN 8 THEN 31
    WHEN 10 THEN 31
    WHEN 12 THEN 31
    WHEN 4 THEN 30
    WHEN 6 THEN 30
    WHEN 9 THEN 30
    WHEN 11 THEN 30
   END
 RETURN @Ngay
END

Ví dụ 3: Bạn xem tiếp ví dụ sau để xác định thứ trong tuần của một giá trị kiểu ngày

CREATE FUNCTION fuThu
(
@ngay DATETIME
)
RETURNS NVARCHAR(10)
AS
BEGIN
DECLARE @KetQua NVARCHAR(10)
SELECT @KetQua=CASE DATEPART(DW,@ngay)
WHEN 1 THEN N’Chủ nhật’
WHEN 2 THEN N’Thứ hai’
WHEN 3 THEN N’Thứ ba’
WHEN 4 THEN N’Thứ tư’
WHEN 5 THEN N’Thứ năm’
WHEN 6 THEN N’Thứ sáu’
ELSE N’Thứ bảy’
END
RETURN (@KetQua)  /* Trị trả về của hàm */
END

Một hàm khi đã được định nghĩa có thể được sử dụng như các hàm do hệ quản trị cơ sở dữ liệu cung cấp (thông thường trước tên hàm ta phải chỉ định thêm tên của người sở hữu hàm bằng dbo.) như ví dụ dưới đây:

SELECT e.FirstName, e.LastName,
dbo.fuThu(e.BirthDate) AS ThuOfBirth
FROM Employees e

Bạn có thể tham khảo thêm bài viết Xử lý từ khóa tìm kiếm cho bài viết

Tiếp theo mình sẽ nói về Hàm với giá trị trả về là “dữ liệu kiểu bảng”

Nếu đã biết về SQL chắc hẳn bạn đã biết cách tạo View từ các bảng trong CSDL, Nhưng với View bạn không thể truyền các tham số được, điều này phần nào đó làm giảm tính linh hoạt trong việc sử dụng View. Vậy nên khi bạn cần sử dụng dữ liệu dạng View mà có các tham số thì việc sử dụng hàm là một giải pháp hợp lý nhất.
Ví dụ 4: Giả sử Mình tạo 1 View như sau:

 CREATE VIEW vProducts
 as
 SELECT    
 Categories.CategoryID,
 Categories.CategoryName,
 Products.ProductName,
 Products.QuantityPerUnit,
 Products.UnitPrice
FROM        
 Categories INNER JOIN
 Products ON Categories.CategoryID = Products.CategoryID
 WHERE Categories.CategoryID=1

Bạn xem ví dụ bạn thấy rằng mình tạo ra 1 View vProducts có  Categories.CategoryID=1 và bạn muốn truy vấn các trường của bảng Products ừng với CategoryID=1 và bạn chỉ cần câu lệnh Select * from vProducts là bạn đã có kết quả như ý. Nhưng với những CategoryID khác thì View vProducts  không làm được trừ phi bạn. Vậy bạn thử sử dụng hàm sau để làm minh họa nhé

Ví dụ 5: Tạo một hàm trả về dữ liệu dạng bảng tùy theo giá trị của biến @CategoryID truyền vào:

CREATE FUNCTION fuGetProducts
(
@CategoryID int
)RETURNS TABLE
AS
RETURN
(
SELECT
Categories.CategoryID,
Categories.CategoryName,
Products.ProductName,
Products.QuantityPerUnit,
Products.UnitPrice
FROM
Categories INNER JOIN
Products ON Categories.CategoryID = Products.CategoryID
WHERE Categories.CategoryID=@CategoryID)

chạy thử hàm trên(Chú ý là khi hàm trả về dạng bảng bạn cũng coi đó như 1 table hoặc 1 View và bạn có thể truy vấn theo 1 hay nhiều trường của hàm) như sau:

SELECT CategoryID,
CategoryName,
ProductName,
QuantityPerUnit,
UnitPrice
FROM  dbo.fuGetProducts(1)
Sẽ tra về dữ liệu chính là Select * from vProducts ở trên. Nếu muốn lầy Theo CategoryID=2 bạn dùng câu lệnh Select * from dbo.fuGetProducts(2)…

Trên đây chỉ là một ví dụ nhỏ về hàm trả lại dữ liệu kiểu bảng hy vọng bạn sẽ hiểu phần nào về loại hàm này. Trong thực tế chúng ta sẽ cần nó để thực hiện các yêu cầu phức tạp hơn tùy vào dữ liệu thiết kế, quan hệ dữ liệu và yêu cầu mà bạn viết hàm để sử dụng

2. Trigger

Cũng tương tự như thủ tục lưu trữ (Stored Prodedure), một trigger là một đối tượng chứa một tập các câu lệnh SQL và tập các câu lệnh này sẽ được thực thi khi trigger được gọi. Điểm khác biệt giữa thủ tục lưu trữ và trigger là: Các thủ tục lưu trữ được thực thi khi người sử dụng có lời gọi đến chúng còn các trigger lại được “gọi” tự động khi xảy ra những giao tác làm thay đổi dữ liệu trong các bảng.

Mỗi một trigger được tạo ra được gắn liền với một bảng nào đó trong cơ sở dữ liệu của bạn. Khi dữ liệu trong bảng bị thay đổi (Là khi xảy ra các sự kiện INSERT, UPDATE hay DELETE) thì trigger sẽ được tự đông kích hoạt. Để xem các Trigger của một bảng trong SQL 2005 bạn chọn bảng đó, chọn Triggers

Sử dụng trigger một cách hợp lý trong cơ sở dữ liệu sẽ có tác động rất lớn trong việc tăng hiệu năng của cơ sở dữ liệu. Các  trigger thực sự hữu dụng với những khả năng sau:

  • Một trigger có thể nhận biết, ngăn chặn và huỷ bỏ được những thao tác làm thay đổi trái phép dữ liệu trong cơ sở dữ liệu.
  • Các thao tác trên dữ liệu (xoá, cập nhật và bổ sung) có thể được trigger phát hiện ra và tự động thực hiện một loạt các thao tác khác trên cơ sở dữ liệu nhằm đảm bảo tính hợp lệ của dữ liệu.
  • Thông qua trigger, ta có thể tạo và kiểm tra được những mối quan hệ phức tạp hơn giữa các bảng trong cơ sở dữ liệu mà bản thân các ràng buộc không thể thực hiện được.

Khi xảy ra ra một sự kiện thao tác dữ liệu một bản ghi trong CSDL nó sẽ lưu ra một bản ghi trong Trigger nó có tên là inserted đối với các thao tác Insert hay Update và deleted đối với Delete
Cú pháp chung để tạo một Trigger như sau:

CREATE TRIGGER Ten_Trigger
ON Ten_Bang
FOR {[INSERT] | [UPDATE] | [DELETE]}
AS
BEGIN
Cac_Cau_Lenh_Cua_Trigger
END
Như vậy khi tạo ra một trigger ta phải chỉ rõ là tạo ra trigger trên table nào và được trigger khi nào (insert, update hay delete. Sau chữ AS là các câu lệnh SQL xử lý công việc, có thể dùng cặp Begin … End hoặc không). Bạn có thể tham khảo thêm bài viết về Trigger Xây dựng cơ sở dữ liệu – TRIGGER

Giờ ta sẽ tìm hiều ví dụ để hiều hơn về Trigger nhé.
Vẫn với CSDL Northwind giả sử trong bảng Employees mình thêm 1 trường là Age – là tuổi của Employees. Giờ mình sẽ viết 1 trigger gắn với bảng Employees để khi thay đổi BirthDay thì trường Age sẽ tự động được cập nhật. Bạn xem ví dụ sau.

CREATE TRIGGER trigCalcAge
ON Employees
FOR  UPDATE, Insert
AS
BEGIN
DECLARE @age int
DECLARE @EmployeeID int
SELECT
@age=YEAR(GETDATE())-year(BirthDate),
@EmployeeID=EmployeeID
FROM inserted
IF UPDATE (BirthDate)
UPDATE Employees
SET Age = @age
WHERE EmployeeID=@EmployeeID
END
Xem ví dụ trên bạn thấy khi có thay đổi nó sẽ tạo 1 bản ghi inserted và chúng ta có thể lấy các giá trị của bản ghi đó. Khi thay đổi dữ liệu bạn sẽ không cần cập nhật trường Age.
Tương tự như vậy với trường hợp Xóa dữ liệu.
Một điều chú ý là với Trigger nó chỉ thực hiện với sự thay đổi dữ liệu của từng bản ghi. Với trường hợp cập nhật dữ liệu theo bó thì khi đó bạn cần các kỹ thuật xử lý phức tạp hơn.
Ví dụ câu lệnh sau: Update Employees Set BirthDate=’12/12/1990 12:00:00 AM’ Câu lệnh này sẽ update toàn bộ dữ liệu (Cập nhật theo bó) của bảng Employees  Nhưng khi đó Trigger của ta chỉ update trường Age ở bản ghi đầu tiên. Để xử lý trường hợp này có nhiều cách chẳng hạn như dùng vòng lặp (dùng con trỏ).

Trong bài viết này mình chỉ giới thiệu qua về Trigger như  vậy để bạn hiểu và ứng dụng nó vào database và yêu cầu của Project của bạn theo yêu cẩu cụ thể

Trong bài viết sau của loại bài này mình sẽ giới thiệu cách xây dựng lớp trong C# để thực thi các Stored Prodecure trong trang asp.net

Một số tài liệu cho bạn tham khảo:

1. Lập trình cơ sở dữ liệu SQL server
2. Câu lệnh truy vấn SQL server
3. Giáo trình thực hành SQL server

Hy vọng bài viết có ích đối với bạn. Chúc bạn thành công!

Đối với một lập trình viên đã có nhiều kinh nghiệm thường sẽ sử dụng Store procedure để thực hiện thao tác dữ liệu thay vì viết câu lênh trực tiếp (dạng Text). Trong bài này mình sẽ giới thiệu về Store Procedure – một phần khá quan trong trong lập trình với cơ sở dữ liệu

Stored Procedure (SP) là  ?

Stored Procedure (Thủ tục lưu trữ) là một đối tượng trong cơ sở dữ liệu bao gồm một tập nhiều câu lệnh SQL được nhóm lại với nhau thành một nhóm với những khả năng sau:

  • Các cấu trúc điều khiển (IF, WHILE, FOR) có thể được sử dụng trong thủ tục.
  • Bên trong thủ tục lưu trữ có thể sử dụng các biến như trong ngôn ngữ lập trình nhằm lưu giữ các giá trị tính toán được, các giá trị được truy xuất được từ cơ sở dữ liệu.
  • Một tập các câu lệnh SQL được kết hợp lại với nhau thành một khối lệnh bên trong một thủ tục. Một thủ tục có thể nhận các tham số truyền vào cũng như có thể trả về các giá trị thông qua các tham số (như trong các ngôn ngữ lập trình). Khi một thủ tục lưu trữ đã được định nghĩa, nó có thể được gọi thông qua tên thủ tục, nhận các tham số truyền vào, thực thi các câu lệnh SQL bên trong thủ tục và có thể trả về các giá trị sau khi thực hiện xong.

Lợi ích khi sử dụng SP

  • Khả năng module hoá: Bạn có thể tạo một SP một lần và tái sử dụng nó bao nhiêu lần tùy thích trong chương trình của bạn. Điều này làm tăng khả năng bảo trì ứng dụng của bạn và cho phép các ứng dụng truy cập vào các cơ sở dữ liệu trong một cách thức thống nhất và tối ưu hóa.
  • Đơn giản hoá các thao tác trên cơ sở dữ liệu nhờ vào khả năng module hoá các thao tác này.
  • Thủ tục lưu trữ được phân tích, tối ưu khi tạo ra nên việc thực thi chúng nhanh hơn nhiều so với việc phải thực hiện một tập rời rạc các câu lệnh SQL tương đương theo cách thông thường.
  • Thủ tục lưu trữ cho phép chúng ta thực hiện cùng một yêu cầu bằng một câu lệnh đơn giản thay vì phải sử dụng nhiều dòng lệnh SQL. Điều này sẽ làm giảm thiểu sự lưu thông trên mạng.
  • Thay vì cấp phát quyền trực tiếp cho người sử dụng trên các câu lệnh SQL và trên các đối tượng cơ sở dữ liệu, ta có thể cấp phát quyền cho người sử dụng thông qua các thủ tục lưu trữ, nhờ đó tăng khả năng bảo mật đối với hệ thống.
  • SP có thể được gọi từ các chương trình phía client hoặc từ các SP khác do đó SP được ưa chuộng trong lập trình CSDL, đặc biệt là trong các ứng dụng theo mô hình N-tier và các Web services.

Tạo thủ tục lưu trữ
Thủ tục lưu trữ được tạo bởi câu lệnh CREATE PROCEDURE với cú pháp như sau:

CREATE PROCEDURE Tên_thủ_tục [(Danh_sách_tham_số)]
[WITH RECOMPILE  |  ENCRYPTION  |  RECOMPILEENCRYPTION]
AS
Các_câu_lệnh_của_thủ_tục

Trong đó: – Tên_thủ_tục: Tên của thủ tục cần tạo. Tên phải tuân theo qui tắc định danh và không được vượt quá 128 ký tự.
– Danh_sách_tham_số: Các tham số của thủ tục được khai báo ngay sau tên thủ tục và nếu thủ tục có nhiều tham số thì các khai báo phân cách nhau bởi dấu phẩy. Khai báo của mỗi một tham số tối thiểu phải bao gồm hai phần: Tên tham số được bắt đầu bởi dấu @, Kiểu dữ liệu của tham số
–  RECOMPILE: Thông thường, thủ tục sẽ được phân tích, tối ưu và dịch sẵn ở lần gọi đầu tiên. Nếu tuỳ chọn WITH RECOMPILE được chỉ định, thủ tục sẽ được dịch lại mỗi khi được gọi.
– ENCRYPTION: Thủ tục sẽ được mã hoá nếu tuỳ chọn WITH ENCRYPTION được chỉ định. Nếu thủ tục đã được mã hoá, ta không thể xem được nội dung của thủ tục.
Các_câu_lệnh_của_thủ_tục: Tập hợp các câu lệnh sử dụng trong nội dung thủ tục. Các câu lệnh này có thể đặt trong cặp từ khoá BEGIN…END hoặc có thể không + Để thực thi một thủ tục ta thực hiện: Execute Ten_Store_Procedure Danh_sách_các_tham_số (Hoặc EXEC  Ten_Store_Procedure Danh_sách_các_tham_số)
+ Để chỉnh sửa một Stored Procedure có sẵn: sử dụng lệnh Alter Procedure
+ Để xóa một Stored Procedure: sử dụng lệnh Drop Procedure  Ten_Store_Procedure
+ Để đổi tên một Stored Procedure ta dùng lệnh: sp_rename ‘tên_sp_cũ’ ‘tên_sp_mới’
+ Để xem nội dung Stored Procedure ta dùng sp_helptext tên_sp

Ví dụ 1: Tạo Store Procedure không có tham số
Trong định nghĩa chúng ta có [(Danh_sách_tham_số)] Nhưng đôi khi Store có thể không có Danh sách các tham số. Bạn tham khảo ví dụ sau

CREATE PROCEDURE spCategories_GetCate_ViDu1
AS
BEGIN
	SELECT c.CategoryID, 
		c.CategoryName, 
		c.Description 
	FROM Categories c
END

Trong ví dụ trên mình đã tạo một SP là spCategories_GetCate để truy vấn các trường CategoryID, CategoryName, Description của bảng Categories, Bạn có thể chú ý đến cách đặt tên tuy tên của SP là tùy chọn nhưng Theo mình để cho dễ kiểm soát khi Database của bạn lớn Bạn nên đặt cho mình một quy tắc đặt tên cho SP. Mình thường đặt là spTênBảng_ChứcnăngcủaSP vì khi database của bạn có thể sẽ có rất nhiều bảng và rất nhiều store nêu không theo quy tắc khi cần Alter một SP bạn sẽ khó tìm SP đó. (Trong SQL server có hỗ trợ chức năng Filter – Phải chuột vào Store Procedure/Filter/Filter setting sau đó nhập từ khóa cần Filter)

Ví dụ 2: Tạo Store Procedure  có tham số đầu vào.
Trong ví dụ sau ta sẽ tạo 1 SP có tham số đầu vào và truy vấn theo điều kiện của tham số

CREATE PROCEDURE spCategories_GetCate_ViDu2
@CategoryID int
AS
BEGIN
	IF @CategoryID>0
		SELECT c.CategoryID, 
			c.CategoryName, 
			c.Description 
		FROM Categories c 
		WHERE c.CategoryID=@CategoryID
	ELSE
		SELECT c.CategoryID, 
			c.CategoryName, 
			c.Description 
		FROM Categories c 
		ORDER BY c.CategoryID DESC 
END

Trong ví dụ trên bạn thấy có tham số đầu vào là @CategoryID khi @CategoryID =0 thì sẽ truy vấn All Còn khi @CategoryID >0 thì truy vấn theo @CategoryID
Đây cũng thêm một cách mà mình hay dùng để giảm bớt số lượng SP trong database. (Thường thì bạn sẽ phải viết 2 SP một là Select One – @CategoryID >0, 2 là Select All @CategoryID =0). Bạn thấy rằng trong SP trên mình có sử dụng cấu trúc điều khiển IF. Bạn có thể thấy là SQL cũng có thể lập trình và có thể nó sẽ mang lại nhiều lợi ích khi lập trình trên SQL. Tham khảo thêm bài viết sau: Hướng dẫn xây dựng website

Ví dụ 3: Store Procedure có tham số đầu vào – thực hiện Insert, Delete, Update.
Trong Store Procedure sau mình sẽ minh họa việc tạo một SP để thực hiện Thêm, Sửa, Xóa một bản ghi phụ thuộc vào điều kiện của tham số:

CREATE PROCEDURE spCategories_Edit_ViDu3
@Action int,
@CategoryID int, 
@CategoryName nvarchar(50), 
@Description nvarchar(400), 
@Picture image 
AS
BEGIN
IF @Action=0
	DELETE FROM Categories 
	WHERE CategoryID=@CategoryID
ELSE
	BEGIN
		IF @CategoryID=0
		INSERT INTO Categories(
			[CategoryName], 
			[Description], 
			[Picture])
			VALUES (
			@CategoryName,
			@Description,
			@Picture	
			)
		ELSE
			UPDATE Categories
			SET
				CategoryName = @CategoryName,
				Description = @Description,
				Picture = @Picture
			WHERE CategoryID=@CategoryID
	END
END

Trong ví dụ trên bạn thấy mình sử dụng 1 Store để thực hiện 3 hành động thêm, sửa, xóa một bản ghi trong bảng Categorys. Cũng là một cách mình muốn giới thiệu để giảm bớt số lượng SP trong cơ sở dữ liệu của bạn.Ví dụ 4: Store Procedure có tham số đầu vào, Tham số đầu ra và trả về giá trị

CREATE PROCEDURE spCategories_Edit_ViDu4
@Action int,
@CategoryID int, 
@CategoryName nvarchar(50), 
@Description nvarchar(400), 
@Picture image,
@error nvarchar(1000) output 
AS
BEGIN TRAN 
IF @Action=0
	BEGIN
		DELETE FROM Categories 
		WHERE CategoryID=@CategoryID
		IF @@ERROR<>0
		BEGIN
			SET @error = N'Lỗi: Chưa xóa được'
			IF @@TRANCOUNT>0
				ROLLBACK tran
			GOTO Error
		END
		ELSE
			SET @error = N'Đã xóa bản ghi'
	END

ELSE
	IF @CategoryID=0
	BEGIN
		INSERT INTO Categories(
		[CategoryName],[Description],[Picture])
		VALUES (
		@CategoryName,@Description,@Picture	
		)
		IF @@ERROR<>0
		BEGIN
			SET @error = N'Lỗi: Lỗi khi thêm dữ liệu'
			IF @@TRANCOUNT>0
				ROLLBACK tran
			GOTO Error
		END
		ELSE
			SET @error = N'Đã thêm mới bản ghi'
	END
	ELSE
		BEGIN
			UPDATE Categories
			SET
				[CategoryName] = @CategoryName,
				[Description] = @Description,
				[Picture] = @Picture
			WHERE CategoryID=@CategoryID
			IF @@ERROR<>0
			BEGIN
				SET @error = N'Lỗi: Lỗi khi cập nhật dữ liệu'
				IF @@TRANCOUNT>0
					ROLLBACK tran
				GOTO Error
			END	
			ELSE
			SET @error = N'Đã cập nhật dữ liệu'
		END
	RETURN @error -- Select @error as ThongBao

COMMIT TRAN 	
Error:

Bạn để ý thấy rằng trong khi thực hiện SP trên thì ở trường hợp nào bạn cũng sẽ nhận được giá trị trả về tương ứng thông báo cho bạn biết trạng thái thực thi. Bạn cũng nên lưu ý sử dụng biên Output trong SP và dùng cách dùng ROLLBACK tran. Khi trong SP của bạn thực hiện nhiều hành động (Có thể là thực hiện Edit dữ liệu nhiều bảng khác nhau trong 1 SP khi đã thực hiện được 1 lệnh nào đó và đến lệnh tiếp theo xảy ra lỗi khi đó nếu  bạn không dùng dùng ROLLBACK thì sẽ xảy ra sai dữ liệu mà lại khó tím lỗi). Tham khảo thêm bài viết:Những thói quen tốt khi viết câu lệnh SQL. Trong những bài viết sau của loạt bài hướng dẫn này mình sẽ giới thiệu cách dùng C# để thực thi một Store Procedure

Trên đây mình giới thiệu qua về Store Procedure, hy vọng qua đây bạn đã hiểu hơn về SP và biết cách tạo SP cho Database của bạn. Trong bào viết sau của loạt bài này mình sẽ giới thiệu về Function và Trigger trong SQL server. Tham khảo thêm bài viết Hướng dẫn xây dựng website :: Xây dựng cơ sở dữ liệu – TRIGGER Một số tài liệu cho bạn tham khảo:
1. Lập trình cơ sở dữ liệu SQL server
2. Câu lệnh truy vấn SQL server
3. Giáo trình thực hành SQL server

Nguồn: http://www.hmweb.com.vn