BEGIN END
ELSE
BEGIN END
Ví dụ: Tạo thủ tục liệt kê hóa đơn theo tháng và năm.
CREATE PROC spHD ( @Thang SmallInt, @Nam SmallInt )
As
If (@Thang <1 Or @Thang>12)
begin
Print 'Thang Sai'
return 0
end
Select * From Orders
Where Year(NgayLap) = @Nam And Month(NgayLap)= @Thang
Go
-3Hàm CASE:
Trả về giá trị chọn lựa từ nhiều điều kiện khác nhau
CASE có 2 dạng :
-
Hàm CASE đơn giản so sánh 1 biểu thức với tập các biểu thức đơn giản để xác định kết quả.
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[ELSE else_result_expression ]
END
Ví dụ: Simple Case
Create Proc spr_ThuTrongTuan
(@D As SmallDateTime, @Thu VarChar(10) OUTPUT)
As
Set @Thu = Case Datepart(w, @D)
When 1 Then 'Chu Nhat'
When 2 Then 'Thu Hai'
When 3 Then 'Thu Ba'
When 4 Then ‘Thu Tu’
When 5 Then ‘Thu Nam’
When 6 Then ‘Thu Sau’
Else 'Thu Bay'
End
Go
-
Hàm CASE tìm kiếm (searched CASE) kiểm tra các biểu thức điều kiện để xác định kết quả.
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[ELSE else_result_expression ]
END
Ví dụ: Xây dựng thủ tục trả về số ngày trong tháng khi biết tháng và năm
Create Proc spr_Days (@Thang Int, @Nam Int)
As
Declare @SN Int
Set @SN = Case
When @Thang In (1,3,5,7,8,10,12) Then 31
When @Thang In (4,6,9,11) Then 30
When @Nam % 4 = 0 Then 29
Else 28
End
Return @SN
Go
- Có thể lồng Case:
Create Proc spr_NgayTrongThang (@Thang Int, @Nam Int)
As
Declare @SN Int
Set @SN = Case
When @Thang In (1,3,5,7,8,10,12) Then 31
When @Thang In (4,6,9,11) Then 30
Else Case
When @Nam % 4 = 0 Then 29
Else 28
End
End
Return @SN
Go
Thực hiện thủ tục:
Declare @SN int
Exec @SN = spr_ngaytrongthang 2,2000
print 'So ngay trong thang 2/2002 la ' + Cast(@SN As Char)
-4Phát biểu WHILE :
WHILE được dùng nhiều trong kiểu dữ liệu CURSOR. Thông thường WHILE thường sử dụng để duyệt từ mẫu tin đầu tiên đến mẫu tin cuối cùng hoặc ngược lại.
WHILE
BEGIN
[CONTINUE] [BREAK]
END
Ví dụ: Tạo thủ tục tính tổng S = 1 + 3 + 5 +… + (2N-1)
Create Proc TongLe ( @N int )
AS
DECLARE @I int, @S int
SET @I = 1
SET @S = 0
WHILE @I <= 2*@N -1
BEGIN
--WAITFOR DELAY '00:00:00.001'
Set @S = @S + @I
Set @I = @I + 1
END
Return @S
Gọi thực hiện:
declare @s int
Exec @s = tongle 3
print ‘Tong la : ‘ + @s
Phát biểu WAITFOR : WAITFOR DELAY <’time’> | TIME <’time’>
Khi sử dụng WHILE, có thể dùng thêm lệnh WAITFOR với thời gian cho trước.
Tham số DELAY dùng chỉ thị khoản thời gian chờ nhất định. Thời gian tính bằng ‘h:m:s’, lớn nhất là 24 giờ.
Tham số TIME: chỉ thị hệ thống chờ cho đến thời gian được chỉ định.
V-Sửa Xóa Thủ Tục Lưu Trữ:
-1Thu thập thông tin về Thủ tục được lưu:
Để xem nội dung câu lệnh Create Proc của thủ tục được lưu, bạn có thể dùng EM hay chạy thủ tục hệ thống sp_helftext với tên thủ tục được lưu.
sp_helptext spAuthors
-2Đổi Tên TTLT: sp_Rename ,
-3Xóa Thủ tục được lưu:
Giống như xóa các đối tượng khác, chúng ta lại sử dụng phát biểu Drop.
DROP PROC[EDURE]
-4Thay đổi thủ tục lưu trữ:
ALTER PROC[EDURE] procedure_name [;number]
[Danh sách tham số]
[WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION]
AS
sql_statement
GO
VI-Xử lý lỗi trong SP:
Để tăng hiệu năng của stored procedures, Bạn nên chứa mã trả về trạng thái thành công hay thất bại của thủ tục. Nếu thất bại thì thông báo về lỗi đã xảy ra.
Để kiểm tra lỗi bạn sử dụng hàm @@Error. Để trả về thông tin về lỗi bạn có thể sử dụng lệnh Return hoặc lệnh Raiserror.
-
Hàm @@ERROR
Hàm @@ERROR chứa mã lỗi cho phát biểu vừa thực hiện. Nó tự động xóa và đặt lại mã lỗi với mỗi phát biểu được thực hiện. Giá trị 0 nếu phát biểu thành công.
Bạn có thể dùng hàm @@ERROR để xác định mã lỗi hoặc làm điều kiện kết thúc thủ tục.
Ví dụ: Chèn Hoá đơn mới, nếu ngày lập là chủ nhật thì chuyển thành ngày thứ 2
CREATE PROC spThemHD ( @MaHD SmallInt, @NgayLap SmallDateTime )
As
If (IsDate(@NgayLap) = 0)
Return 0
If DatePart(w, @NgayLap) = 1
Set @NgayLap = @NgayLap + 1
Insert Into HoaDon(MaHD, NgayLap) Values(@MaHD, @NgayLap)
If (@Error <> 0)
Begin
RaiseError(‘Loi them hoa don moi’,10,1)
Return 0
End
-
Lệnh RAISERROR
Phát biểu RAISERROR dùng xuất thông báo lỗi lấy từ table sysmessages hoặc một thông báo với nội dung nào đó
RAISERROR ( { msg_id | msg_str } { , severity , state } [ , argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
Arguments
-
msg_id: Mã số > 50000 của dòng thông báo do người dùng tạo trên table sysmessages bằng thủ tục sp_AddMessage
-
msg_str: Nội dung thông báo lỗi cần xuất. .
-
severity: Mã số chỉ định mức độ nghiên trọng của lỗi.
Mức độ từ 0-19 được xem như là thông tin phản hồi. Mức từ 0 đến 16: lỗi phát sinh do dữ liệu. Mức 10 là lbáo cáo trạng thái. Mức từ 11 - 16 là những lỗi có thể được hiệu chỉnh bởi user.
Mức từ 20 - 25 chỉ định các lỗi tác hại từ ứng dụng không thể phục hồi. Nếu thông báo mức này, kết nối từ Client đến Server sẽ bị ngắt.
-
State: là số nguyên từ 1 đến 127 biểu diễn thông tin về trạng thái thỉnh cầu của lỗi., nhằm chỉ rõ lỗi thuộc nhóm nào trong hệ thống lỗi. Giá trị phủ nhận của state là 1.
-
Argument: Là tham số dùng thay thế vào những biến được sử dụng trong chuỗi msg_str hoặc thông báo tương ứng với msg_id. Mỗi tham số thay thế có thể là 1 biến cục bộ hoặc giá trị thuộc kiểu: int1, int2, int4, char, varchar, binary, or varbinary.
RAISERROR ('The level for job_id:%d should be between %d and %d.',
16, 1, @@JOB_ID, @@MIN_LVL, @@MAX_LVL)
-
Option: có thể chứa các giá trị sau:
Value
|
Description
|
LOG
|
Ghi lỗi vào file nhật ký.
|
NOWAIT
|
Gởi thông báo ngay lập tức đến client.
|
SETERROR
|
Đặt giá trị msg_id hay 50000 cho hàm @@ERROR.
|
Ví dụ: RAISERROR('Invalid member number', 10, 1) WITH LOG
Khi xuất hiện lỗi, mã lỗi được đặt trong hàm @@ERROR. Mặc định @@ERROR được đặt bằng 0 cho các lỗi có severity từ 1 đến 10.
-
Thêm Thông báo lỗi vào bảng sysmessages:
-aDùng thủ tục sp_addmessage để thêm vào bảng sysmessages. .
sp_AddMessage @msgnum = msg_id ,
@severity = severity ,
@msgtext = 'msg'
[ , @lang = 'language' ]
[ , @with_log = 'True | False' ]
[ , @replace = 'REPLACE' ]
msg_id: phải có giá trị từ 50.001 trở lên.
’REPLACE’: dùng chỉ định lệnh sẽ thay thế msg_id nếu msg_id đã có trong bảng.
Example: USE master
EXEC sp_addmessage @msgnum = 60000, @severity = 16,
@msgtext = ‘Kiểu dữ liệu không hợp lệ’ , @lang = 'us_english', @with_log = 'true'
-bĐể xóa thống báo lỗi : sử dụng sp_dropmessage
sp_DropMessage @msgnum = msg_id
Chú thích – Comments
Có 2 cách chú thích: chú thích trên cùng dòng lệnh hoặc chú thích trên nhiều dòng.
Chú thích trên cùng dòng với một phát biểu - In-Line Comments
Sử dụng 2 dấu trừ (--) trước lời chú thích.
Chú thích trên nhiều dòng – Block Comments
Bắt đầu bởi /* và kết thúc bởi dấu */
