آموزش گام‌به‌گام SQL Server با دیتابیس نمونه‌ی AdventureWorks | از مبتدی تا پیشرفته | (قسمت ۹)

درس ۹: Stored Procedure و پارامترها

🎯 هدف درس:

در این درس یاد می‌گیرید که Stored Procedure چیست، چگونه ساخته می‌شود و چطور می‌توان برای آن پارامتر ورودی و خروجی تعریف کرد. این بخش از SQL برای خودکارسازی کوئری‌ها، افزایش سرعت اجرا و حفظ امنیت بسیار مهم است.

📘 مفهوم نظری:

Stored Procedure در واقع مجموعه‌ای از دستورات SQL است که در پایگاه داده ذخیره می‌شود و می‌توان آن را مانند یک تابع اجرا کرد. مزیت آن این است که بارها بدون نیاز به نوشتن مجدد کوئری‌ها قابل استفاده است.

CREATE PROCEDURE usp_GetAllProducts
AS
BEGIN
    SELECT ProductID, Name, ListPrice
    FROM Production.Product;
END;

EXEC usp_GetAllProducts;

CREATE PROCEDURE usp_GetProductsByPrice
    @MinPrice DECIMAL(10,2)
AS
BEGIN
    SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE ListPrice > @MinPrice;
END;

EXEC usp_GetProductsByPrice @MinPrice = 1000;

CREATE PROCEDURE usp_GetProductsByRange
    @MinPrice DECIMAL(10,2),
    @MaxPrice DECIMAL(10,2)
AS
BEGIN
    SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE ListPrice BETWEEN @MinPrice AND @MaxPrice;
END;

EXEC usp_GetProductsByRange @MinPrice = 500, @MaxPrice = 2000;

CREATE PROCEDURE usp_CountEmployees
    @Total INT OUTPUT
AS
BEGIN
    SELECT @Total = COUNT(*)
    FROM HumanResources.Employee;
END;

DECLARE @Result INT;
EXEC usp_CountEmployees @Total = @Result OUTPUT;
SELECT @Result AS EmployeeCount;

ALTER PROCEDURE usp_GetProductsByPrice
    @MinPrice DECIMAL(10,2)
AS
BEGIN
    SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE ListPrice > @MinPrice
    ORDER BY ListPrice DESC;
END;

DROP PROCEDURE usp_GetAllProducts;

CREATE PROCEDURE usp_CheckPrice
    @ProductID INT
AS
BEGIN
    DECLARE @Price DECIMAL(10,2);
    SELECT @Price = ListPrice FROM Production.Product WHERE ProductID = @ProductID;

    IF @Price > 1000
        PRINT 'Expensive Product';
    ELSE
        PRINT 'Normal Product';
END;

CREATE PROCEDURE usp_GetTopProducts
    @Count INT = 5
AS
BEGIN
    SELECT TOP (@Count) ProductID, Name, ListPrice
    FROM Production.Product
    ORDER BY ListPrice DESC;
END;

CREATE PROCEDURE usp_SafeDeleteProduct
    @ProductID INT
AS
BEGIN
    BEGIN TRY
        DELETE FROM Production.Product
        WHERE ProductID = @ProductID;
    END TRY
    BEGIN CATCH
        PRINT 'Error: Unable to delete product.';
    END CATCH
END;

sp_helptext 'usp_GetProductsByRange';

🧠 تمرین‌های عملی:

• تمرین ۱: ساخت Stored Procedure برای نمایش ۱۰ محصول گران‌تر از مقدار ورودی.
• تمرین ۲: ساخت Stored Procedure با دو پارامتر ورودی برای محدوده قیمت.
• تمرین ۳: ساخت Stored Procedure برای شمارش کارمندان با پارامتر خروجی.
• تمرین ۴: افزودن شرط IF برای بررسی گران یا ارزان بودن محصول.
• تمرین ۵ (چالشی): ساخت Procedure حذف ایمن (Safe Delete) با TRY…CATCH.

💡 پاسخ تمرین‌های عملی:

-- تمرین ۱
CREATE PROCEDURE usp_TopExpensiveProducts
    @MinPrice DECIMAL(10,2)
AS
BEGIN
    SELECT TOP (10) ProductID, Name, ListPrice
    FROM Production.Product
    WHERE ListPrice > @MinPrice
    ORDER BY ListPrice DESC;
END;

-- تمرین ۲
CREATE PROCEDURE usp_ProductRange
    @Min DECIMAL(10,2),
    @Max DECIMAL(10,2)
AS
BEGIN
    SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE ListPrice BETWEEN @Min AND @Max;
END;

-- تمرین ۳
CREATE PROCEDURE usp_CountEmployees
    @Total INT OUTPUT
AS
BEGIN
    SELECT @Total = COUNT(*) FROM HumanResources.Employee;
END;

-- تمرین ۴
CREATE PROCEDURE usp_CheckProductPrice
    @ProductID INT
AS
BEGIN
    DECLARE @Price DECIMAL(10,2);
    SELECT @Price = ListPrice FROM Production.Product WHERE ProductID = @ProductID;

    IF @Price > 1000
        PRINT 'Expensive';
    ELSE
        PRINT 'Cheap';
END;

-- تمرین ۵ (چالشی)
CREATE PROCEDURE usp_SafeDeleteProduct
    @ProductID INT
AS
BEGIN
    BEGIN TRY
        DELETE FROM Production.Product WHERE ProductID = @ProductID;
        PRINT 'Product deleted successfully.';
    END TRY
    BEGIN CATCH
        PRINT 'Error deleting product.';
    END CATCH
END;

📚 جمع‌بندی درس:

در این درس یاد گرفتید چگونه با استفاده از Stored Procedure عملیات تکراری را در پایگاه داده ذخیره و بارها اجرا کنید. همچنین با پارامترهای ورودی، خروجی و مدیریت خطا آشنا شدید. در درس بعد، با موضوع بهینه‌سازی کوئری‌ها و ایندکس‌ها (Indexing) کار خواهیم کرد تا سرعت اجرا و کارایی SQL Server را افزایش دهیم.