1. GIỚI THIỆU

Stored Procedure là một nhóm 1 hoặc nhiều câu lệnh Transact-SQL có vai trò như một khối mã lệnh thực hiện một tác vụ cụ thể, đã được biên dịch và lưu trữ trong SQL Server dưới một cái tên do người tạo đặt và được xử lý như một đơn vị. Thủ tục có thể có tham số hoặc không, tham số có 2 loại là đầu vào và đầu ra.

2. ƯU ĐIỂM CỦA THỦ TỤC

• Tăng cường bảo mật
• Biên dịch trước
• Giảm băng thông giữa client và server
• Thực thi nhanh
• Tái sử dụng mã nguồn

3. TẠO VÀ GỌI THỦ TỤC

• Cú pháp:

CREATE { PROC | PROCEDURE } procedure_name

[ { @parameter data_type } ]

[ { @parameter data_type output } ]

AS

<TSQL_statement>

[return expression]

Trong đó:

procedure_name: chỉ ra tên của thủ tục.

@parameter: chỉ ra tham số vào/ra của thủ tục.

data_type: chỉ ra kiểu dữ liệu của tham số.

TSQL_statement: là một hay nhiều câu lệnh Transact-SQL.

• Một số ví dụ:

/*

Tạo thủ tục lấy ra thông tin sách gồm bookid, title, author, price

dữ liệu được sắp xếp giảm dần theo price

*/

create proc sp_GetBooks

as

select bookid, title, author, price

from books order by price desc

--thực thi thủ tục trong sql server

exec sp_GetBooks

/*

Tạo thủ tục lấy ra thông tin sách theo tham số truyền vào là bookid

*/

create proc sp_GetBookById

@bookid varchar(10)

as

select * from books

where bookid=@bookid

--thực thi thủ tục trong sql server

exec sp_GetBookById 'CT01'

/*

Tạo thủ tục tính tổng trị giá

tham số đầu vào publisherid

tham số đầu ra totalprice

*/

create proc sp_CalculateBookPrice

@publisherid int,

@totalprice float output

as

select @totalprice=sum(price) from books where PublisherId=@publisherid

--thực thi thủ tục trong sql server

declare @totalprice float

exec sp_CalculateBookPrice 1,@totalprice output

print @totalprice

/*

Tạo thủ tục thêm mới Publishers

Nếu trùng tên thì output ra một thông báo lỗi và return

*/

create proc sp_InsertPublisher

@publishername nvarchar(100),

@phone varchar(100),

@address nvarchar(100),

@error nvarchar(100)=null output--gán giá trị mặc định là null

as

if exists(select * from Publishers where PublisherName = @publishername)

begin

set @error=N'Trùng tên nhà xuất bản'

return

end

insert into Publishers values(@publishername,@phone,@address)

--thực thi thủ tục trong sql server

declare @msg nvarchar(100)

exec sp_InsertPublisher N'NXB Trẻ','097834342',N'238 Hoàng Quốc Việt',@msg output

if @msg is not null

print @msg

/*

Tạo thủ tục trả về tổng số sách của một nhà xuất bản

Tham số đầu vào publisherid

return total of books

*/

create proc sp_CalculateTotalBooks

@publisherid int

as

declare @total int

select @total=count(*) from books where publisherid=@publisherid

return @total

--thực thi thủ tục trong sql server

declare @total int

exec @total=sp_CalculateTotalBooks 1

print @total

/*

Lưu ý: trong thủ tục lệnh return sẽ trả về 1 số nguyên,

mặc định giá trị trả về là 0

*/

4. MỘT SỐ LỆNH KHÁC VỀ THỦ TỤC

• Sửa thủ tục

/*

Sửa thủ tục sp_GetBooks sắp xếp giá tăng dần

*/

alter proc sp_GetBooks

as

select bookid, title, author, price

from books order by price

• Xóa thủ tục

/*

xóa thủ tục sp_GetBooks

*/

drop proc sp_GetBooks

• Mã hóa thủ tục

/*

Sửa và thêm tùy chọn mã thủ tục sp_GetBooks, bạn có thể thêm tùy chọn lúc tạo thủ tục

*/

alter proc sp_GetBooks

with encryption

as

select bookid, title, author, price

from books order by price

5. FILE SCRIPT SQL ĐỂ THỰC HÀNH

• Link download: BookStore.sql

Nếu muốn tìm hiểu sâu về thủ tục hay contact với tôi nhé.