Lấy Về Giá Trị Từ Thủ Tục Bằng Output Parameter

Vũ Huy Tâm

Một tình huống tương đối thường gặp là bạn cần viết một thủ tục để ghi vào một bản ghi mới (ví dụ khách hàng), và trả về ID của bản ghi vừa được tạo (ID của khách hàng mới đó). SQL Server cung cấp một loại tham số gọi là output parameter dùng với thủ tục, để dùng trong tình huống trên. Để có thể dùng được output parameter cần liên quan đến cả hai phía: viết thủ tục và thực thi thủ tục.
Khi viết thủ tục, ở đoạn khai báo các tham số bạn cần đưa từ khóa “OUTPUT” vào sau tham số đó. Ở ví dụ sau, tham số @ID được khai báo là output parameter:

CREATE PROC dbo.ThemKhachHang
@Ten NVARCHAR(100),
@ID INT OUTPUT
AS
...

Đồng thời khi thực thi thủ tục bạn cũng cần đưa từ khóa “OUTPUT” vào tham số tương ứng trong lời gọi thủ tục:

DECLARE @ID INT
EXEC dbo.ThemKhachHang @Ten='Nguyen Van A', @ID OUTPUT
...

Dưới đây là ví dụ hoàn chỉnh:

CREATE TABLE dbo.KhachHang (KH_ID INT PRIMARY KEY IDENTITY, KH_Ten NVARCHAR(100))
GO
CREATE PROC dbo.ThemKhachHang
@Ten NVARCHAR(100),
@ID INT OUTPUT
AS
BEGIN
	INSERT dbo.KhachHang(KH_Ten) VALUES(@Ten)
	SET @ID = SCOPE_IDENTITY()
END
 
-- Gọi thủ tục
DECLARE @KH_ID INT
EXEC dbo.ThemKhachHang 'Nguyen Van A', @KH_ID OUTPUT
PRINT 'Khach hang ID:'
PRINT @KH_ID

Trong ví dụ trên tôi gọi thủ tục từ bên trong SQL Server. Khi gọi thủ tục từ ứng dụng (ví dụ .Net) công việc cũng tương tự như vậy: Bạn cũng cần khai báo một biến và truyền cho thủ tục với lựa chọn là output parameter; Khi thực hiện xong, biến đó sẽ chứa giá trị mà thủ tục gửi trả lại.

Bàn thêm về các cách làm khác
Nói chung khi muốn thủ tục gửi về một hoặc vài giá trị, cách làm dùng output parameter là thích hợp nhất. Một vài cách làm khác là dùng lệnh RETURN hoặc SELECT ở cuối thủ tục.
Bạn có thể đưa lệnh RETURN @ID vào cuối thủ tục trên và ứng dụng cũng sẽ nhận được giá trị @ID. Nhưng cách này chỉ có thể trả về một giá trị. Khi bạn cần lấy về hai giá trị hoặc nhiều hơn nữa thì nó không đáp ứng được. Ngoài ra có một lý do khác không nên dùng RETURN (và đây là lý do chính), là lệnh RETURN được tạo ra với mục đích trả về trạng thái kết quả (status) của thủ tục, ví dụ 0 là bình thường, nhỏ hơn 0 là lỗi… Trong một tình huống nào đó giá trị cần trả về là số âm, có thể ứng dụng sẽ nghĩ thủ tục đã có lỗi, mặc dù nó thực hiện thành công. Vì thế bạn không nên lạm dụng (overload) lệnh RETURN cho các mục đích khác. Microsoft cũng khuyến cáo không nên dùng RETURN để trả về dữ liệu.

Bạn cũng có thể đưa lệnh SELECT @ID vào cuối thủ tục, nó sẽ gửi về một tập kết quả chứa một bản ghi. Ở phía ứng dụng bạn cũng cần viết thêm code để đọc dữ liệu từ tập bản ghi này. Cách làm này tốn kém hơn vì tập bản ghi có overhead lớn hơn là một (hoặc vài) giá trị đơn lẻ, phía ứng dụng cũng cần nhiều code xử lý hơn. Nói chung khi chỉ cần lấy về một vài giá trị, bạn nên dùng output parameter. Còn khi có quá nhiều giá trị cần trả về, output parameter trở nên rườm rà quá thì bạn có thể chuyển sang dùng SELECT.




Tags: ,

13 Comments
Posted on 15/12/2010 | Categories: SQL Server Programming, Stored Procedure

Các bài viết tương tự

Comments
  • Nguyen Anh (06/07/2011 8:11 pm)

    Chào bạn !
    Bạn có thể cho mình hỏi là trong sql có kiểu dữ liệu nào để lưu một mảng danh sách kết quả ko ?
    Ví dụ khi sử dụng while mình có các kết quả như thể này, mình muốn trả các kết quả này về dạng table

    declare @songuyen int
    @set @songuyen = 0
    while(@songuyen <100)
    begin
    @songuyen = @songuyen+1
    end

  • Vũ Huy Tâm (07/07/2011 10:53 am)

    Tôi không hiểu ý bạn lắm. Sau đoạn lệnh trên thì biến @songuyen có giá trị 100. Vậy bạn cần trả danh sách kết quả nào?

  • Nguyen Anh (10/07/2011 12:12 am)

    declare @songuyen int
    @set @songuyen = 0
    while(@songuyen <100)
    begin
    @songuyen = @songuyen+1
    //lưu lại kết quả biến @songuyen
    end

    mình muốn trong lúc chạy vòng lặp while để lưu giữ lại kết quả biến @songuyen

  • Vũ Huy Tâm (11/07/2011 2:28 pm)

    à vậy bạn dùng một biến kiểu bảng (hoặc bảng tạm) để lưu kết quả:

    declare @songuyen int
    declare @t table(i int)
    @set @songuyen = 0
    while(@songuyen <100)
    begin
    @songuyen = @songuyen+1
    //lưu lại kết quả biến @songuyen
    insert into @t(i) values(@songuyen)
    end
    select i from @t

  • luu duc anh (22/08/2011 5:30 am)

    Chào bạn Mình có code này

     CREATE PROCEDURE n_goicuoc_add

    @ma_goi as char(2),
    @ten_goi as char(50),
    @tam as char(1) output,
    @mo_ta as varchar(200)

    as

    IF NOT EXISTS(select 1 from tb_goicuoc where ma_goi= @ma_goi)

    BEGIN
    set @tam=’K’ — Ma goi cuoc khong trung
    insert into dbo.[tb_goicuoc](ma_goi,ten_goi,mo_ta) values(@ma_goi,@ten_goi,@mo_ta)
    END

    ELSE

    BEGIN
    set @tam=’T’ — Ma goi cuoc nay bi trung
    print ‘Ma cuoc bi trung: ‘
    END

    GO

    mình muốn lấy kết quả giá trị biến @tam xuat trong VB6 thì sao bạn

  • Vũ Huy Tâm (23/08/2011 4:59 pm)

    Hàh, cái này chắc bạn phải hỏi bên VB6 forum. Lâu lắm rồi tôi không nhớ nữa

  • Nguyễn Văn Cường (12/04/2012 1:29 am)

    Chào mọi người trong diễn đàn,mình có 1 câu hỏi rất mong nhận được sự trợ giúp của mọi người!
    Mình có 2 bảng sau :
    NHANVIEN (idnv,tennv,gioitinh,idpb)
    PHONGBAN(idpb,tenpb)
    Và mình có 1 thủ tục insert sau :

    @manv char(10)
    @tennv nvarchar(50)
    @gt char(10)
    @mapb char(10)
    as
    begin
    if exists(select idpb from PHONGBAN where idpb=@mapb)
    insert into NHANVIEN (idnv,tennv,gioitinh,idpb) values (@manv,@tennv,@gt,@mapb)
    else
    Print ‘Không tồn tại mã phòng ban bên bảng phòng ban nên không thể thêm được’
    end
    Go

    Sao mình chạy thủ tục này và mình nhập idpb ở bên bảng NHANVIEN không có bên bảng PHONGBAN thì nó ko hiện thông báo như dòng –Print ‘Không tồn tại mã phòng ban bên bảng phòng ban nên không thể thêm được’– nhỉ ? MÌnh cũng đã gán 1 biến lấy giá trị rùi xét if mà vẫn ko được mong nhận được sự giúp đỡ của mọi người.
    Mình xin cảm ơn !

    • Vũ Huy Tâm (12/04/2012 6:56 pm)

      sql server không bắt được lỗi nếu bảng không tồn tại, cho nên đoạn lệnh của bạn không biên dịch được khi gặp dòng “select idpb from PHONGBAN where idpb=@mapb”.

  • Thang Ho (19/04/2012 9:30 pm)

    @Cuong nếu bạn muốn kiểm tra tồn tại id phòng ban theo tôi tốt hơn bạn nên kiểm tra trong code.

  • Lê-Lợi (24/04/2014 11:18 am)

    Chào !
    Mình có 1 vấn đề muốn được giải đáp.
    - Có 3 table product(pro_Name…),customer(cus_Name),comment(…..)
    Vấn Đề :
    Tạo 1 proc với 1 tham số truyền vào @name
    – -if @name co like %% trong truong pro_Name on table Product thi hien thi du lieu tuong ung cua table product
    – – if @name co like %% trong truong cus_Name on table customer thi hien thi du lieu tuong ung cua table customer
    – – if @name khong co trong 2 truong hop tren thi hien thi toan bo du lieu cua bang comment

    Mong được giải đáp sớm . Thank !

  • nguoihanoi (29/04/2014 12:00 pm)

    Sử dụng case

  • Ken Nguyễn (24/06/2016 3:16 am)

    Chào bạn !

    Bạn cho mình hỏi : Mình đang làm Web MVC và sử dụng Entity Framework để thác tác csdl, vậy làm cách nào để mình có thể lấy được 2 hoặc 3 output parameter từ 1 thủ tục ?

    VÍ dụ :

    Mình có 1 thủ tục :

    CREATE PROCEDURE [dbo].[GetProduct]  @ProdId INT OUTPUT

    Ở MVC mình gọi thủ tục lên như sau :

    public ActionResult GetProd()
    {
    // mình khai báo 1 biến truyền vào thủ tục
    ObjectParameter ProdId =null;

    // Sau đó mình gọi thủ tục với biến ProdId và trả dữ liệu biến ProdId về result
    vả result= db.GetProduct(ProdId,…..);
    }

    Nếu là 1 output parameter thì được , còn nếu là 2 hoặc 3 thì sao ?

    • Trí (04/02/2017 3:11 am)

      bạn dung như bên dưới

       CREATE PROCEDURE [dbo].[GetProduct]  @ProdId INT OUTPUT, @para2 INT OUTPUT

      .

Leave a Reply

Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>