Skip to main content

One post tagged with "stored-procedures"

View All Tags

Stored Procedures và Functions trong SQL Server

· 3 min read
admin Hướng Nghiệp Dữ Liệu
admin Hướng Nghiệp Dữ Liệu
Fullstack

Trong bài viết này, chúng ta sẽ tìm hiểu về Stored Procedures và Functions trong SQL Server, hai công cụ mạnh mẽ để tổ chức và tái sử dụng code SQL.

Stored Procedures​

Tạo Stored Procedure cơ bản​

CREATE PROCEDURE sp_GetCustomerOrders
    @CustomerID int
AS
BEGIN
    SELECT 
        o.OrderID,
        o.OrderDate,
        p.ProductName,
        od.Quantity,
        od.UnitPrice
    FROM Orders o
    JOIN OrderDetails od ON o.OrderID = od.OrderID
    JOIN Products p ON od.ProductID = p.ProductID
    WHERE o.CustomerID = @CustomerID;
END;

Stored Procedure với nhiều tham số​

CREATE PROCEDURE sp_InsertOrder
    @CustomerID int,
    @OrderDate datetime,
    @ShipAddress nvarchar(100)
AS
BEGIN
    INSERT INTO Orders (CustomerID, OrderDate, ShipAddress)
    VALUES (@CustomerID, @OrderDate, @ShipAddress);
    
    SELECT SCOPE_IDENTITY() as NewOrderID;
END;

Stored Procedure với OUTPUT​

CREATE PROCEDURE sp_CalculateOrderTotal
    @OrderID int,
    @Total decimal(18,2) OUTPUT
AS
BEGIN
    SELECT @Total = SUM(Quantity * UnitPrice)
    FROM OrderDetails
    WHERE OrderID = @OrderID;
END;

Functions​

Scalar Functions​

CREATE FUNCTION fn_CalculateDiscount
(
    @Price decimal(18,2),
    @DiscountPercent decimal(5,2)
)
RETURNS decimal(18,2)
AS
BEGIN
    DECLARE @DiscountAmount decimal(18,2);
    SET @DiscountAmount = @Price * (@DiscountPercent / 100);
    RETURN @Price - @DiscountAmount;
END;

Table-Valued Functions​

CREATE FUNCTION fn_GetProductInventory
(
    @MinQuantity int
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        p.ProductID,
        p.ProductName,
        p.UnitsInStock,
        c.CategoryName
    FROM Products p
    JOIN Categories c ON p.CategoryID = c.CategoryID
    WHERE p.UnitsInStock >= @MinQuantity
);

Multi-Statement Table-Valued Functions​

CREATE FUNCTION fn_GetSalesByPeriod
(
    @StartDate date,
    @EndDate date
)
RETURNS @SalesTable TABLE
(
    ProductID int,
    ProductName nvarchar(100),
    TotalQuantity int,
    TotalAmount decimal(18,2)
)
AS
BEGIN
    INSERT INTO @SalesTable
    SELECT 
        p.ProductID,
        p.ProductName,
        SUM(od.Quantity) as TotalQuantity,
        SUM(od.Quantity * od.UnitPrice) as TotalAmount
    FROM Products p
    JOIN OrderDetails od ON p.ProductID = od.ProductID
    JOIN Orders o ON od.OrderID = o.OrderID
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY p.ProductID, p.ProductName;
    
    RETURN;
END;

Sử dụng Stored Procedures và Functions​

Gọi Stored Procedure​

-- Gọi với tham số đơn
EXEC sp_GetCustomerOrders @CustomerID = 1;

-- Gọi với OUTPUT
DECLARE @Total decimal(18,2);
EXEC sp_CalculateOrderTotal @OrderID = 1, @Total = @Total OUTPUT;
SELECT @Total as OrderTotal;

Sử dụng Functions​

-- Scalar Function
SELECT 
    ProductName,
    UnitPrice,
    dbo.fn_CalculateDiscount(UnitPrice, 10) as DiscountedPrice
FROM Products;

-- Table-Valued Function
SELECT * FROM fn_GetProductInventory(10);

-- Multi-Statement Table-Valued Function
SELECT * FROM fn_GetSalesByPeriod('2023-01-01', '2023-12-31');

Best Practices​

  1. Đặt tên có tiền tố (sp_ cho Stored Procedures, fn_ cho Functions)
  2. Sử dụng tham số thay vì hardcode giá trị
  3. Xử lý lỗi với TRY-CATCH
  4. Tối ưu hiệu suất
  5. Ghi chú đầy đủ

Kết luận​

Stored Procedures và Functions là những công cụ quan trọng trong SQL Server, giúp tổ chức code và tăng tính tái sử dụng. Trong bài viết tiếp theo, chúng ta sẽ tìm hiểu về Index và tối ưu hiệu suất trong SQL Server.