Stored Procedure چیست و چرا از آن استفاده می‌کنیم؟

Stored Procedure (یا به‌اختصار SP) مجموعه‌ای از دستورات SQL است که با یه نام مشخص روی سرور ذخیره می‌شه و می‌تونیم هر وقت لازم بود اجراش کنیم. مثل یه تابع تو برنامه‌نویسی: پارامتر می‌گیره، منطقی اجرا می‌کنه، و ممکنه خروجی بده یا تغییراتی تو دیتابیس ایجاد کنه.

چرا Stored Procedure؟

• قابلیت استفاده مجدد: یه بار می‌نویسی، هر جا لازم بود صدا می‌زنی.
• امنیت بالا: کاربر فقط می‌تونه SP رو اجرا کنه، بدون دسترسی مستقیم به جداول.
• کارایی بهتر: پلن اجرا (Execution Plan) کش می‌شه و سرعت اجرا رو بالا می‌بره.
• نگهداری ساده: منطق پیچیده رو تو یه جا متمرکز می‌کنه.
• کاهش ترافیک شبکه: چند دستور SQL رو یه‌جا روی سرور اجرا می‌کنه.
• لایه منطق تجاری: SP می‌تونه مثل یه لایه بین اپلیکیشن و دیتابیس عمل کنه و منطق تجاری (مثل محاسبه حقوق یا اعتبارسنجی) رو مدیریت کنه.

معایب

• Parameter Sniffing: گاهی پلن اجرا برای داده‌های مختلف بهینه نیست.
• نگهداری: تو پروژه‌های بزرگ نیاز به نسخه‌بندی داره.
• محدودیت‌ها: برای عملیات پیچیده مثل ETL، ابزارهای دیگه (مثل SSIS) ممکنه بهتر باشن.

تفاوت با Function

• Stored Procedure: می‌تونه داده‌ها رو تغییر بده (INSERT/UPDATE/DELETE) و چند Result Set برگردونه. نمی‌تونی مستقیم تو SELECT ازش استفاده کنی.
• Function: معمولاً یه خروجی مشخص داره و می‌تونی تو SELECT ازش استفاده کنی.

---

سینتکس پایه

CREATE PROCEDURE [dbo].[ProcedureName]
    @Param1 INT,
    @Param2 VARCHAR(50) = 'default', -- مقدار پیش‌فرض
    @OutParam INT OUTPUT              -- پارامتر خروجی
AS
BEGIN
    SET NOCOUNT ON; -- حذف پیام‌های اضافی
    -- کد SQL
END;

---

مثال‌های کاربردی

۱. دریافت اطلاعات کارمند با ID

CREATE PROCEDURE dbo.GetEmployeeById
    @EmployeeID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM dbo.Employees
    WHERE EmployeeID = @EmployeeID;
END;

اجرا:

EXEC dbo.GetEmployeeById 42;

۲. افزودن کارمند جدید با خروجی ID

CREATE PROCEDURE dbo.AddEmployee
    @FirstName NVARCHAR(100),
    @LastName NVARCHAR(100),
    @Salary MONEY,
    @NewEmployeeID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.Employees (FirstName, LastName, Salary)
    VALUES (@FirstName, @LastName, @Salary);
    SET @NewEmployeeID = SCOPE_IDENTITY();
END;

اجرا:

DECLARE @id INT;
EXEC dbo.AddEmployee 'Ali', 'Rezaei', 2000, @id OUTPUT;
SELECT @id AS CreatedId;

۳. تراکنش امن با TRY/CATCH

CREATE PROCEDURE dbo.TransferSalary
    @FromEmpID INT,
    @ToEmpID INT,
    @Amount MONEY
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRAN;
    BEGIN TRY
        UPDATE dbo.Employees
        SET Salary = Salary - @Amount
        WHERE EmployeeID = @FromEmpID;
        IF @@ROWCOUNT = 0
            THROW 50001, 'From employee not found', 1;

        UPDATE dbo.Employees
        SET Salary = Salary + @Amount
        WHERE EmployeeID = @ToEmpID;
        IF @@ROWCOUNT = 0
            THROW 50002, 'To employee not found', 1;

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRAN;
        THROW;
    END CATCH;
END;

۴. Dynamic SQL امن

CREATE PROCEDURE dbo.SearchEmployees
    @ColumnName SYSNAME,
    @SearchText NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'SELECT EmployeeID, FirstName, LastName
                 FROM dbo.Employees
                 WHERE ' + QUOTENAME(@ColumnName) + N' LIKE @p';
    EXEC sp_executesql @sql, N'@p NVARCHAR(100)', @p = N'%' + @SearchText + '%';
END;

۵. استفاده از Table-Valued Parameter

CREATE TYPE dbo.IdList AS TABLE (Id INT PRIMARY KEY);

CREATE PROCEDURE dbo.GetEmployeesByIds
    @Ids dbo.IdList READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT e.EmployeeID, e.FirstName, e.LastName
    FROM dbo.Employees e
    JOIN @Ids i ON e.EmployeeID = i.Id;
END;

اجرا:

DECLARE @t dbo.IdList;
INSERT INTO @t (Id) VALUES (1), (5), (7);
EXEC dbo.GetEmployeesByIds @t;

---

مقایسه با SELECT مستقیم

ویژگی	Stored Procedure	SELECT مستقیم
کارایی	پلن اجرا کش می‌شه، سرعت بیشتر	هر بار ممکنه پلن جدید ساخته بشه
امنیت	دسترسی فقط به SP با GRANT EXECUTE، کاهش ریسک SQL Injection	نیاز به دسترسی مستقیم به جدول، ریسک امنیتی بیشتر
نگهداری	تغییر تو یه جا، مدیریت ساده	تغییر تو همه کدها، پیچیده‌تر
استفاده مجدد	یه‌بار تعریف، بارها استفاده	تکرار کدها تو جاهای مختلف
لایه منطق تجاری	منطق (مثل محاسبه یا اعتبارسنجی) تو SP متمرکزه	منطق پخش تو اپلیکیشن

امنیت

• SELECT مستقیم: کاربر باید دسترسی مستقیم به جدول داشته باشه (مثل SELECT روی Employees). این باعث افزایش سطح دسترسی و ریسک SQL Injection می‌شه.
• Stored Procedure: می‌تونی فقط اجازه اجرای SP رو بدی (مثل GRANT EXECUTE ON dbo.GetEmployeeById TO SomeUser;). اینطوری کاربر بدون دسترسی به جدول‌ها، فقط SP رو اجرا می‌کنه و امنیت خیلی بالاتر می‌ره.

نگهداری و توسعه

• SELECT مستقیم: اگه کوئری‌ها تو اپلیکیشن پخش باشن، برای هر تغییر باید همه‌جا رو اصلاح کنی.
• Stored Procedure: منطق تو یه SP متمرکزه. تغییر فقط تو SP اعمال می‌شه و همه‌جا به‌روز می‌شه.

قابلیت استفاده مجدد

• SELECT مستقیم: باید همون کوئری رو بارها تو جاهای مختلف بنویسی.
• Stored Procedure: یه‌بار تعریف می‌شه و هر جا لازم باشه صدا زده می‌شه.

لایه منطق تجاری (Business Logic Layer)

SPها می‌تونن به‌عنوان یه لایه بین دیتابیس و اپلیکیشن عمل کنن:

• فقط SPها به داده‌ها دسترسی دارن.
• منطق تجاری (مثل محاسبه حقوق، گزارش‌گیری، یا اعتبارسنجی) تو SP نوشته می‌شه.
• اپلیکیشن فقط از طریق SP با دیتابیس ارتباط برقراره، که کنترل و مدیریت رو ساده‌تر می‌کنه.

---

نکات حرفه‌ای

• همیشه SET NOCOUNT ON رو بذار تا پیام‌های اضافی حذف بشن.
• برای مدیریت خطاها از TRY/CATCH و THROW استفاده کن.
• تو Dynamic SQL از sp_executesql و QUOTENAME استفاده کن تا از SQL Injection جلوگیری بشه.
• دسترسی‌ها رو محدود کن: فقط GRANT EXECUTE به کاربر بده.
• مراقب Parameter Sniffing باش و در صورت نیاز از OPTION (RECOMPILE) استفاده کن.
• SPها رو تو پروژه‌های بزرگ نسخه‌بندی و تست کن.