CREATE PROC – Lệnh tạo Stored Procedure

--Tạo thủ tục
CREATE PROC Tên_thủ_tục
Khai_báo_tham_số Kiểu_dữ_liệu
AS
	Lệnh A
	Lệnh B
	...
GO
--Hoặc
CREATE PROCEDURE Tên_thủ_tục
Khai_báo_tham_số Kiểu_dữ_liệu
AS
	Lệnh A
	Lệnh B
	...
GO

--Gọi thực hiện thủ tục
EXEC Tên_thủ_tục Tham_số_truyền_vào
--Hoặc
EXECUTE Tên_thủ_tục Tham_số_truyền_vào

--Tạo thủ tục
CREATE PROC Tang_luong
AS
	UPDATE EMPLOYEES SET SALARY = SALARY * 1.1
GO

--Gọi thực hiện thủ tục
EXEC Tang_luong

--Tạo thủ tục
CREATE PROC Tang_luong @Phan_tram int
AS
	DECLARE @Ty_le decimal(3,1) = 1 + @Phan_tram / 100
	UPDATE EMPLOYEES SET SALARY = SALARY * @Ty_le
GO

--Gọi thực hiện thủ tục
EXEC Tang_luong @Phan_tram = 10

--Gọi thực hiện thủ tục: Tạo login
USE master
GO
EXEC sp_addlogin @loginame = 'tom', @passwd = 'tom123'
GO

--Gọi thực hiện thủ tục: Tạo user
USE HumanResource
GO
EXEC sp_adduser @loginame = 'tom', @name_in_db = 'tom'
GO

--Tạo thủ tục
CREATE PROC Tong @a int, @b int
AS
	--Khai báo biến
	DECLARE @Tong int
	--Tính tổng
	SET @Tong = @a + @b
	--In kết quả tổng
	PRINT CONCAT(N'Tổng của ', @a, N' và ', @b, N' là: ', @Tong)
GO

--Gọi thực hiện thủ tục
EXEC Tong 1, 2

--Tạo thủ tục
CREATE PROC Tong @a int, @b int
AS
	--Khai báo biến
	DECLARE @Tong int
	--Tính tổng
	SET @Tong = @a + @b
	--In kết quả
	PRINT N'Tổng là: ' + STR(@Tong)
GO

--Gọi thực hiện thủ tục
EXEC Tong 1, 2

--Tạo thủ tục
CREATE PROC Dem_nhan_vien @depid int
AS
	--Bỏ message (x row(s) affected)
	SET NOCOUNT ON
	--Khai báo biến
	DECLARE @Dem int
	--Đếm số nhân viên của phòng
	SELECT @Dem = COUNT(*)	FROM EMPLOYEES WHERE Department_id = @depid
	--In kết quả đếm được
	PRINT N'Tổng số nhân viên: ' + STR(@Dem)
GO

--Gọi thực hiện thủ tục
EXEC Dem_nhan_vien 80

--Tạo thủ tục
CREATE PROC Tong @a int, @b int, @Tong int OUT
AS
	--Tính tổng
	SET @Tong = @a + @b
GO

-- Gọi thực hiện thủ tục
DECLARE @Tong int
EXEC Tong 1, 2, @Tong OUT
PRINT N'Tổng là: ' + STR(@Tong)

--Tạo thủ tục
CREATE PROC Dem_nhan_vien @depid int, @Dem int OUT
AS
	--Bỏ message (x row(s) affected)
	SET NOCOUNT ON
	--Đếm số nhân viên của phòng
	SELECT @Dem = COUNT(*) FROM EMPLOYEES WHERE department_id = @depid
GO

--Gọi thực hiện thủ tục
DECLARE @Dem int
EXEC Dem_nhan_vien 80, @Dem OUT
PRINT N'Tổng là: ' + STR(@Dem)

--Tạo thủ tục
CREATE PROC In_thong_tin_SV @Ma_sinh_vien char(3)
AS
	--Bỏ message (x row(s) affected)
	SET NOCOUNT ON
	DECLARE @Ho_ten nvarchar(50)
	--
	SELECT @Ho_ten = Ho_sinh_vien + ' ' + Ten_sinh_vien
	FROM SINH_VIEN
	WHERE Ma_sinh_vien = @Ma_sinh_vien
	--
	PRINT N'Họ tên: ' + @Ho_ten
GO

--Gọi thực hiện thủ tục
EXEC In_thong_tin_SV C01
--Hoặc
EXEC In_thong_tin_SV @Ma_sinh_vien = 'C01'
--Hoặc
DECLARE @Ma_sinh_vien char(3) = 'C01'
EXEC In_thong_tin_SV @Ma_sinh_vien

--Tạo thủ tục
CREATE PROC Doc_thong_tin_SV
@Ma_sinh_vien char(3),
@Ho_sinh_vien nvarchar(30) OUT,
@Ten_sinh_vien nvarchar(20) OUT
AS
    --Bỏ message (x row(s) affected)
    SET NOCOUNT ON
    --
    SELECT @Ho_sinh_vien = Ho_sinh_vien, @Ten_sinh_vien = Ten_sinh_vien
    FROM SINH_VIEN
    WHERE Ma_sinh_vien = @Ma_sinh_vien
GO

--Gọi thực hiện thủ tục
DECLARE @Ma_sinh_vien char(3) = 'C01'
DECLARE @Ho_sinh_vien nvarchar(30), @Ten_sinh_vien nvarchar(20)
EXEC Doc_thong_tin_SV @Ma_sinh_vien, @Ho_sinh_vien OUT, @Ten_sinh_vien OUT
PRINT N'Họ tên: ' + @Ho_sinh_vien + ' ' + @Ten_sinh_vien

--Tạo thủ tục
CREATE PROC In_so_nhan_vien @depid int = NULL
AS
	DECLARE @Dem int
	--Đếm số nhân viên của phòng
	SELECT @Dem = COUNT(*)
	FROM EMPLOYEES
	WHERE Department_id = @depid OR @depid IS NULL
	--
	PRINT N'Số nhân viên: ' + STR(@Dem)
GO

--Gọi thực hiện thủ tục
--1. In số nhân viên phòng 80
EXEC In_so_nhan_vien @depid = 80
--2. In số nhân viên tất cả các phòng
EXEC In_so_nhan_vien

--Định nghĩa kiểu TABLE
CREATE TYPE Kieu_NCC AS TABLE
(
	Ma char(3) PRIMARY KEY,
	Ten nvarchar(100),
	Dia_chi nvarchar(200)
)
GO

--Biến kiểu TABLE
DECLARE @Danh_sach Kieu_NCC
--
INSERT INTO @Danh_sach VALUES('T05', N'Công ty Hừng Sáng', N'246 An
Dương Vương')
INSERT INTO @Danh_sach VALUES('T06', N'Công ty Tầm Cao', N'123 Nguyễn Trãi')
INSERT INTO @Danh_sach VALUES('T07', N'Công ty Hoàng Hôn', N'345 Bình Thới')
--
SELECT * FROM @Danh_sach

--Tham số kiểu TABLE
CREATE PROC Them_NCC @Danh_sach Kieu_NCC READONLY
AS
SET NOCOUNT ON
--
INSERT INTO NHACC(MaNhaCC, TenNhaCC, DiaChi)
SELECT Ma, Ten, Dia_chi FROM @Danh_sach
GO

--Tham số kiểu TABLE
DECLARE @Danh_sach Kieu_NCC
--
INSERT INTO @Danh_sach VALUES('T05', N'Công ty Hừng Sáng', N'246 An
Dương Vương')
INSERT INTO @Danh_sach VALUES('T06', N'Công ty Tầm Cao', N'123 Nguyễn Trãi')
--
EXEC Them_NCC @Danh_sach
GO

--Biến kiểu XML
--Khai báo biến kiểu XML
DECLARE @Danh_sach XML, @Chuoi NVARCHAR(1000)
--Gán dữ liệu XML cho biến
SET @Chuoi = '<DANH_SACH><SO Gia_tri="12"/>' + '<SO Gia_tri="34"/>' +
'<SO Gia_tri="21"/>' + '</DANH_SACH>'
SET @Danh_sach = CAST(@Chuoi AS XML)
--In
PRINT CAST(@Danh_sach AS NVARCHAR(1000))
SELECT @Danh_sach

--Biến kiểu XML
--Khai báo biến kiểu XML
DECLARE @Danh_sach XML, @Chuoi NVARCHAR(1000)
--Gán dữ liệu XML cho biến
SET @Chuoi = '<DANH_SACH><SO Gia_tri="12"/>' + '<SO Gia_tri="34"/>' +
'<SO Gia_tri="21"/>' + '</DANH_SACH>'
SET @Danh_sach = CAST(@Chuoi AS XML)
--Truy vấn
SELECT So.value('@Gia_tri', 'integer') Gia_tri
FROM @Danh_sach.nodes('/DANH_SACH/SO') AS Danh_sach (So)

--Tham số kiểu XML
CREATE PROC spud_Tong @Danh_sach XML, @Tong INT OUT
AS
SET NOCOUNT ON
--Tính tổng
SELECT @Tong = SUM(So.value('@Gia_tri', 'integer'))
FROM @Danh_sach.nodes('/DANH_SACH/SO') AS Danh_sach (So)
GO

--Tham số kiểu XML
DECLARE @Danh_sach XML, @Chuoi NVARCHAR(1000)
--Gán dữ liệu XML cho biến
SET @Chuoi = '<DANH_SACH><SO Gia_tri="12"/>' + '<SO Gia_tri="34"/>' + '<SO
Gia_tri="21"/>' + '</DANH_SACH>'
SET @Danh_sach = CAST(@Chuoi AS XML)
--Gọi thực hiện thủ tục
DECLARE @Tong INT
EXEC spud_Tong @Danh_sach, @Tong OUT
PRINT N'Tổng là: ' + STR(@Tong)

--Tham số kiểu XML
ALTER PROC spud_Tong @Danh_sach XML, @Ket_qua XML OUT
AS
DECLARE @Tong INT
--Tính tổng
SELECT @Tong = SUM(So.value('@Gia_tri', 'integer'))
FROM @Danh_sach.nodes('/DANH_SACH/SO') AS Danh_sach (So)
--Trả ra kết quả
DECLARE @Chuoi NVARCHAR(1000) = '<KET_QUA Tong="{0}" />'
SET @Chuoi = REPLACE(@Chuoi,'{0}',@Tong)
SET @Ket_qua = CAST(@Chuoi AS XML)
GO

--Tham số kiểu XML
DECLARE @Danh_sach XML, @Chuoi NVARCHAR(1000)
--Gán dữ liệu XML cho biến ...
--Gọi thực hiện thủ tục
DECLARE @Tong INT, @Ket_qua XML
EXEC spud_Tong @Danh_sach, @Ket_qua OUT
--In kết quả tổng
SELECT @Tong = Dong.value('@Tong', 'integer')
FROM @Ket_qua.nodes('/KET_QUA') AS Ket_qua (Dong)
PRINT N'Tổng là: ' + STR(@Tong)

--Khai báo biến kiểu CURSOR
DECLARE Tên_biến CURSOR
--Gán giá trị cho biến
SET Biến = CURSOR FOR Câu_lệnh_SELECT

--Ví dụ
--Khai báo biến
DECLARE @cur CURSOR
--Gán giá trị
SET @cur = CURSOR FOR
SELECT Ho_sinh_vien + ' ' + Ten_sinh_vien AS Ho_ten, Ngay_sinh, Gioi_tinh
FROM SINH_VIEN WHERE Ma_sinh_vien='C01'

--Khai báo tham số OUT kiểu CURSOR trong thủ tục
Tên_tham_số CURSOR VARYING OUTPUT
--Xử lý trong thủ tục
1. Gán giá trị cho tham số OUT kiểu CURSOR
2. Mở CURSOR

CREATE PROC Cursor_Test @Ma_sinh_vien CHAR(3),
@cur CURSOR VARYING OUTPUT
AS
--Gán
SET @cur = CURSOR FOR
SELECT Ho_sinh_vien + ' ' + Ten_sinh_vien AS Ho_ten, Ngay_sinh,
Gioi_tinh FROM SINH_VIEN WHERE Ma_sinh_vien=@Ma_sinh_vien
--Mở
OPEN @cur
GO

--Khai báo biến kiểu CURSOR
DECLARE @cur CURSOR
--Gọi thực hiện thủ tục, truyền vào biến @cur và nhận lại một CURSOR
EXEC Cursor_Test 'C01', @cur OUT
--Tiếp tục...

EXEC sp_help Tên_thủ_tục
EXEC sp_helptext Tên_thủ_tục

CREATE PROC Tên_thủ_tục
Khai_báo_tham_số Kiểu_dữ_liệu
WITH ENCRYPTION
AS
    Lệnh A
    Lệnh B
    ...
GO

--Có ba cách để biên dịch thủ tục
--1.
CREATE PROC Tên_thủ_tục Khai_báo_tham_số
WITH RECOMPILE
AS...
--2.
EXEC Tên_thủ_tục WITH RECOMPILE
--3.
EXEC sp_recompile Tên_thủ_tục