پروسیجر-SP-Store Procedure-Proc

در این مقاله به‌صورت مفصل به بررسی Stored Procedure d یا (SP) در SQL Server می‌پردازیم. از تعریف و کاربردها تا نکات پیشرفته و نمونه‌های کامل کد، همه‌چیز را پوشش خواهیم داد. در انتها نیز به سؤالات متداول (FAQ) پاسخ می‌دهیم تا تصویری کامل از این ابزار قدرتمند پایگاه داده به دست آورید.

Stored Procedure چیست؟

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

مزایای استفاده از Stored Procedure

  • قابلیت استفادهٔ مجدد: یک بار تعریف می‌شود و در برنامه‌های مختلف قابل فراخوانی است.
  • بهبود امنیت: با اعطای دسترسی اجرا (EXECUTE) می‌توان دسترسی به جداول حساس را محدود کرد.
  • بهینه‌سازی عملکرد: پلان اجرایی Cached شده و بار CPU را کاهش می‌دهد.
  • کاهش ترافیک شبکه: به‌جای ارسال چندین کوئری، تنها یک بار فراخوانی SP کفایت می‌کند.

ساختار کلی Stored Procedure

یک SP با دستور CREATE PROCEDURE تعریف می‌شود و با ALTER و DROP قابل ویرایش یا حذف است.

CREATE PROCEDURE dbo.MyProcedure
    @Param1 INT,
    @Param2 NVARCHAR(50)
AS
BEGIN
    -- بدنهٔ دستورها
    SELECT @Param1 AS Value1, @Param2 AS Value2;
END;
GO
  • dbo.MyProcedure : نام SP و اسکیما
  • @Param1, @Param2 : پارامترهای ورودی
  • بلوک BEGIN...END : گروه‌بندی دستورات

تعریف، تغییر و حذف SP

ایجاد Stored Procedure

CREATE PROCEDURE dbo.GetEmployeeByID
    @EmpID INT
AS
BEGIN
    SELECT * FROM HumanResources.Employee
    WHERE EmployeeID = @EmpID;
END;
GO

ویرایش Stored Procedure

ALTER PROCEDURE dbo.GetEmployeeByID
    @EmpID INT,
    @IncludeSalary BIT = 0
AS
BEGIN
    SELECT 
        e.EmployeeID, e.FirstName, e.LastName,
        CASE WHEN @IncludeSalary = 1 THEN e.Salary END AS Salary
    FROM HumanResources.Employee AS e
    WHERE e.EmployeeID = @EmpID;
END;
GO

حذف Stored Procedure

DROP PROCEDURE IF EXISTS dbo.GetEmployeeByID;
GO

پارامترهای ورودی، خروجی و مقادیر بازگشتی

  • پارامتر ورودی: @Param INT
  • پارامتر خروجی: با کلیدواژه OUTPUT مشخص می‌شود
  • مقدار بازگشتی: با دستور RETURN عدد صحیح بازمی‌گرداند
CREATE PROCEDURE dbo.CalculateSum
    @A INT,
    @B INT,
    @Sum INT OUTPUT
AS
BEGIN
    SET @Sum = @A + @B;
    RETURN 0;  -- کد موفقیت
END;
GO

فراخوانی:

DECLARE @Result INT;
DECLARE @ReturnCode INT;

EXEC @ReturnCode = dbo.CalculateSum
    @A = 5,
    @B = 10,
    @Sum = @Result OUTPUT;

PRINT CONCAT('Sum = ', @Result, ', ReturnCode = ', @ReturnCode);

استفاده از TRY…CATCH و مدیریت خطا

CREATE PROCEDURE dbo.SafeInsert
    @Value NVARCHAR(100)
AS
BEGIN
    BEGIN TRY
        INSERT INTO dbo.Logs (Message, CreatedAt)
        VALUES (@Value, GETDATE());
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(4000) = ERROR_MESSAGE();
        -- ثبت خطا یا عملیات اصلاحی
        RAISERROR('خطا در درج لاگ: %s', 16, 1, @ErrorMsg);
    END CATCH
END;
GO
  • با TRY…CATCH می‌توان خطا را کنترل و پیغام سفارشی صادر کرد.
  • از ERROR_MESSAGE(), ERROR_NUMBER() و دیگر توابع خطا استفاده کنید.

استفاده از تراکنش (Transaction) داخل SP

CREATE PROCEDURE dbo.TransferFunds
    @FromAccount INT,
    @ToAccount INT,
    @Amount DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        UPDATE Accounts SET Balance = Balance - @Amount
        WHERE AccountID = @FromAccount;

        UPDATE Accounts SET Balance = Balance + @Amount
        WHERE AccountID = @ToAccount;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;  -- یا RAISERROR
    END CATCH
END;
GO

امنیت و مجوزها

  • مجوز EXECUTE: برای اجرا باید حق دسترسی داشته باشید.
  • اصول کمترین دسترسی (Least Privilege): SP به داده‌های مورد نیاز محدود شود.
  • محافظت در برابر SQL Injection: استفاده از پارامترها و خودداری از الحاق رشته‌ای به کوئری.
GRANT EXECUTE ON dbo.GetEmployeeByID TO [YourAppRole];

بهینه‌سازی عملکرد

  • استفاده از Parameter Sniffing با OPTION (RECOMPILE) در مواقع ضروری
  • اجتناب از کوئری‌های غیرسخت‌افزاری (e.g. CURSOR) و استفاده از دستورات ستونی
  • بررسی پلان اجرا با SQL Server Management Studio
  • استفاده از SP_RESET_CONNECTION و Optimize for Ad Hoc Workloads

اجرای Dynamic SQL

گاهی لازم است کوئری به‌صورت پویا ساخته شود:

CREATE PROCEDURE dbo.SearchTable
    @TableName SYSNAME,
    @ColumnName SYSNAME,
    @SearchValue NVARCHAR(100)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) =
        N'SELECT * FROM ' + QUOTENAME(@TableName) +
        ' WHERE ' + QUOTENAME(@ColumnName) + ' = @Val';

    EXEC sp_executesql
        @SQL,
        N'@Val NVARCHAR(100)',
        @Val = @SearchValue;
END;
GO
  • استفاده از QUOTENAME مانع از حملات SQL Injection می‌شود.
  • پارامترهای ورودی در sp_executesql بهینه‌تر و امن‌تر هستند.

بهترین شیوه‌ها (Best Practices)

  • اسامی معنادار و استاندارد (e.g. usp_, sp_ خودداری کنید)
  • مستندسازی هر SP در فرم کامنت بالا
  • نگه‌داری نسخهٔ کد در سیستم کنترل نسخه تست واحد (Unit Test) با استفاده از ابزارهایی مثل tSQLt
  • مانیتورینگ زمان اجرا و منابع مصرفی

مقایسه با سایر روش‌های کدنویسی T-SQL

ویژگیStored ProcedureUser-Defined FunctionTrigger
قابلیت Side-effectبلهخیر (به‌جز UDF جدول)بله
بازگشت دادهمی‌تواند جدول یا اسکالر باشداسکالر یا جدولخیر
فراخوانی صریحبا EXECدرون SELECT/WHEREخودکار در رویدادها
مدیریت خطاTRY…CATCHمحدودمحدود

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

گزارش فروش ماهانه

CREATE PROCEDURE dbo.MonthlySalesReport
    @Year INT,
    @Month INT
AS
BEGIN
    SELECT 
        p.ProductName,
        SUM(s.Quantity) AS TotalQuantity,
        SUM(s.Quantity * s.UnitPrice) AS TotalRevenue
    FROM Sales.SalesOrderDetail AS s
    JOIN Production.Product AS p
        ON s.ProductID = p.ProductID
    WHERE YEAR(s.OrderDate) = @Year
      AND MONTH(s.OrderDate) = @Month
    GROUP BY p.ProductName
    ORDER BY TotalRevenue DESC;
END;
GO

سؤالات متداول (FAQ)

۱. Stored Procedure و ویو چه تفاوتی دارند؟

ویو فقط کوئری SELECT ذخیره‌شده است و نمی‌تواند منطق پیچیده یا تراکنش داشته باشد. SP می‌تواند چندین نوع دستور را در برگیرد و پارامتر بپذیرد.

۲. چگونه می‌توان خروجی جدولی از SP دریافت کرد؟

Simply SELECT داخل SP بنویسید. فراخواننده نتیجهٔ SELECT را دریافت می‌کند.

۳. آیا SP در Plan Cache ذخیره می‌شود؟

بله، پلان اجرایی Cached شده و در دفعات بعدی بهینه‌تر اجرا می‌شود.

۴. بهترین روش برای جلوگیری از Parameter Sniffing چیست؟

می‌توان از OPTION (RECOMPILE) یا متغیر محلی که مقدار پارامتر به آن تخصیص داده شود استفاده کرد.

۵. آیا می‌توان SP را در یک تراکنش بزرگ‌تر فراخوانی کرد؟

بله، SP می‌تواند داخل تراکنش مادر اجرا شود. در صورت CATCH، COMMIT یا ROLLBACK همان تراکنش را تحت کنترل دارد.

نتیجه‌گیری

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

ارتباط و مشاوره

برای اطلاعات بیشتر و مشاوره می‌توانید از طریق زیر با ما در ارتباط باشید:

  • تماس ✆ با شرکت لاندا برای مشاوره، اجرا و یا آموزش تخصصی.

روزبه امیرعصامی

SQL Server

SQL Serverاسکیوال سرورایندکسباز طراحی دیتابیسبهینه سازی کوئریپایگاه دادهطراحی دیتابیس

Xلینکدینواتساپتلگرامایمیل

نوشته های مرتبط

نظری داده نشده

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

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *