Stored Procedure Parameters trong SQL Server

Ở bài trước bạn đã học được cách tạo và chạy một stored procedure, vậy thì trong bài này mình sẽ nói đến tham số trong stored, hay còn gọi là Stored Procedure Parameter.

1. Tạo Stored Procedure với một tham số

Trong các ví dụ ở bài trước chúng ta chỉ tạo một procedure đơn giản đó là lấy danh sách sản phẩm, bây giờ giả sử mình cần lấy danh sách sản phẩm có giá lớn hơn một số nào đó thì lúc này ta phải cần truyền vào một tham số parameter. Hãy xem ví dụ dưới đây.

CREATE PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;

Như vậy tham số sẽ nằm trong cặp dấu ngoặc (), mỗi tham số phải khai báo tên và kiểu dữ liệu tương ứng thông qua từ khóa as.

• @min_list_price AS DECIMAL chính là tham số có kiểu dữ liệu DECIMAL.
• Để sử dụng tham số ở bên trong phần thân của procedure thì ta chỉ cần gõ tên của nó là được @min_list_price.

Để thực thi stored procedure này thì ta vẫn sử dụng cách thông thường đó là dùng lệnh EXEC, tuy nhiên bạn phải truyền thêm giá trị cho tham số ở đằng sau nữa nhé. Như ví dụ dưới đây là mình truyền giá trị 100.

Bài viết này được đăng tại [free tuts .net]

<br />
EXEC uspFindProducts 100;

Kết quả nó sẽ hiển sản phẩm có giá lớn hơn 100.

Nếu bạn thay đổi giá trị tham số thành 200 như sau:

EXEC uspFindProducts 200;

Thì kết quả chỉ hiện thị sản phẩm có giá lớn hơn 200.

2. Tạo Stored Procedure với nhiều tham số

Cách làm không khác gì một tham số, bạn chỉ cần đặt các tham số cách nhau bởi dấu phẩy là được. Lưu ý là môi tham số sẽ phải khai báo kiểu dữ liệu tương ứng nhé.

Giả sử giờ mình cần lấy danh sách sản phẩm có giá nằm trong khoảng min và max nào đó thì sẽ sửa lại cấu trúc của procedure như sau:

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL
    ,@max_list_price AS DECIMAL
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price
    ORDER BY
        list_price;
END;

Mình sử dụng lệnh ALTER để sửa procedure chứ không tạo mỗi, trong cặp dấu ngoặc mình tạo ra hai tham số cách nhau bởi dấu phẩy:

• @min_list_price AS DECIMAL là tham số thứ nhất
• @max_list_price AS DECIMAL là tham số thứ hai

Để chạy procedure này thì mình sẽ làm như sau:

EXECUTE uspFindProducts 900, 1000;

Kết quả sẽ trả về sản phẩm có giá nằm trong khoảng từ 900 đến 1000.

3. Tường minh các tham số của Stored Procedure

Việc truyền dữ liêu cho các tham số lúc chạy procedure như trên đôi khi lại gây ra không tường minh bởi bạn phải truyền đúng thứ tự. Có một cách khác cũng khá hay đó là bạn sẽ truyền tên tham số tương ứng với giá trị luôn, lúc này bạn sẽ không cần quan tâm đến thứ tự của chúng.

Mình sẽ chạy lại procedure trong ví dụ ở phần 2 như sau:

EXECUTE uspFindProducts 
    @min_list_price = 900, 
    @max_list_price = 1000;

Tất tường mình, và kết quả không có gì khác so với trước đó.

4. Gán giá trị mặc định cho tham số

Ở các ví dụ trên bạn phải truyền một lúc ba tham số, nhưng đôi khi bạn muốn có thể tùy chọn chạy một vài tham số thồi, còn các tham số còn lại có thể không cần truyền vào thì lúc này nó sẽ lấy giá trị mặc định.

Giá trị là giá trị mặc định tức là nó sẽ nhận giá trị đó nếu không được truyền vào khi chạy procedure. 

Cách làm rất đơn giản, bạn chỉ cần gán giá trị cho nó lúc khai báo tham số.

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL = 0
    ,@max_list_price AS DECIMAL = 999999
    ,@name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;

Như trong ví dụ này thì:

• @min_list_price sẽ có giá trị là 0 nếu bạn không truyền vào
• @max_list_price sẽ có giá trị là 999999 nếu bạn không truyền vào

Vì ta có thể bỏ qua tham số nên khi chạy procedure bạn phải sư dụng cách chạy tường minh như ở phần 3 nhé.

EXECUTE uspFindProducts 
    @name = 'Trek';

Kết quả:

Như vậy là trong bài này bạn đã học được cách sử dụng tham số khi làm việc với stored procedure trong SQL Server, bài này rất quan trọng nên bạn cố gắng nắm vững nhé. hẹn gặp lại các bạn ở bài tiếp theo.