مقدمه

Stored Procedure یکی از بنیادی‌ترین ابزارهای Microsoft SQL Server برای متمرکزکردن منطق داده، کنترل دسترسی و ساخت عملیات قابل استفاده مجدد است. یک رویه می‌تواند پارامتر ورودی بگیرد، چند Query و تراکنش هماهنگ اجرا کند، Result Set بسازد، مقدار OUTPUT تحویل دهد و وضعیت را با RETURN اعلام کند. با این قدرت، مسئولیت طراحی قرارداد، امنیت، مدیریت خطا و پایش کارایی نیز افزایش می‌یابد.

این راهنما چرخه کامل رویه را از ایجاد و تغییر تا اجرا و حذف پوشش می‌دهد. هدف فقط معرفی Syntax نیست؛ ارتباط دستورات با کنترل نسخه، انتشار امن، سازگاری مصرف‌کننده، Execution Plan و مجوزها بررسی می‌شود. برای هر دستور نیز یک مقاله مستقل با ده مثال عملی در دسترس است.

رویه ذخیره‌شده را مانند API پایگاه داده طراحی کنید: نام و قرارداد پایدار، ورودی تایپ‌شده، خروجی مستند، خطای قابل مشاهده و تغییر نسخه‌بندی‌شده.

دسترسی سریع به مقاله‌های تخصصی

• آموزش کامل CREATE PROCEDURE با مثال‌های عملی
• آموزش کامل ALTER PROCEDURE با مثال‌های عملی
• آموزش کامل DROP PROCEDURE با مثال‌های عملی
• آموزش کامل EXEC با مثال‌های عملی
• آموزش کامل EXECUTE با مثال‌های عملی

جایگاه Stored Procedure در معماری داده

در معماری چندلایه، رویه می‌تواند مرز قابل کنترل میان برنامه و جداول باشد. برنامه به‌جای دریافت مجوز مستقیم DML، فقط اجازه اجرای عملیات مشخص را می‌گیرد. این روش Principle of Least Privilege را تقویت می‌کند و ممیزی را ساده‌تر می‌سازد. با این حال، تبدیل همه Queryها به رویه بدون معیار معماری، وابستگی شدید به موتور و هزینه نگهداری ایجاد می‌کند؛ انتخاب باید آگاهانه باشد.

قرارداد رویه از نام Schema-qualified، پارامترها، مقادیر پیش‌فرض، رفتار NULL، Result Setها، OUTPUT، Return Code و خطاها تشکیل می‌شود. تغییر هر جزء می‌تواند Breaking Change باشد. حتی جابه‌جایی یا تغییر نوع ستون خروجی ممکن است نگاشت ORM، گزارش یا فرایند ETL را بشکند. تست قرارداد باید این اجزا را مستقل از جزئیات داخلی کنترل کند.

پارامترها باید تا حد ممکن با نوع و طول ستون‌های مقصد یکسان باشند. ارسال nvarchar به ستون varchar یا طول بسیار بزرگ می‌تواند Conversion ضمنی و تخمین نامناسب Cardinality ایجاد کند. این مسئله گاهی Index Seek را به Scan تبدیل می‌کند. مشاهده هشدارهای Actual Plan و مقایسه Logical Reads راه عملی کشف اثر واقعی است.

امنیت رویه فقط GRANT EXECUTE نیست. Ownership Chain، Dynamic SQL، EXECUTE AS، Cross-database Access و امضای ماژول روی نتیجه اثر دارند. SQL پویای الحاقی می‌تواند هم تزریق SQL و هم دورزدن مدل مجوز را ممکن کند. داده باید با sp_executesql پارامتری و Identifier فقط از فهرست مجاز تولید شود.

چرخه عمر رویه باید در کنترل نسخه باشد. فایل استقرار شامل تعریف کامل، پیش‌شرط‌ها، نسخه هدف، آزمون سلامت و Rollback است. ویرایش دستی Production باعث اختلاف محیط و از بین رفتن قابلیت بازسازی می‌شود. Pipeline انتشار باید اسکریپت را در پایگاه مشابه اجرا و نتیجه Smoke Test را ثبت کند.

CREATE PROCEDURE برای ساخت نخستین نسخه و CREATE OR ALTER برای استقرار تکرارپذیر نسخه مطلوب مناسب است. در نسخه‌های قدیمی SQL Server باید الگوی شرط وجود و ALTER را به‌کار برد. نام رویه نباید با sp_ آغاز شود، زیرا این پیشوند برای رویه‌های سیستمی رزرو و Resolution آن نامطلوب است.

ALTER PROCEDURE هویت شیء و معمولاً مجوزهای مستقیم را حفظ می‌کند، اما می‌تواند قرارداد اجرایی را بشکند. افزودن پارامتر اختیاری معمولاً سازگارتر از پارامتر اجباری است. برای تغییر بزرگ، ساخت نسخه جدید کنار نسخه قدیمی و مهاجرت مرحله‌ای مصرف‌کنندگان از تغییر ناگهانی امن‌تر خواهد بود.

EXEC و EXECUTE از نظر فراخوان رویه هم‌معنا هستند؛ EXEC شکل کوتاه است. پارامترهای نام‌دار خوانایی و مقاومت در برابر تغییر ترتیب را افزایش می‌دهند. Result Set، OUTPUT و RETURN کانال‌های متفاوت‌اند و نباید بدون قرارداد مشخص با هم مخلوط شوند. کد وضعیت عددی جای پیام خطا یا داده تجاری مفصل نیست.

DROP PROCEDURE یک تغییر مخرب است. پیش از حذف باید مصرف‌کنندگان بیرونی، Jobها، گزارش‌ها و Dynamic SQL بررسی شوند. کاتالوگ وابستگی همه مراجع پویا را نمی‌بیند. بازه Deprecation، پایش فراخوان و آماده‌بودن اسکریپت بازگشت، حذف را از یک قمار عملیاتی به تغییر مدیریت‌شده تبدیل می‌کند.

مدیریت تراکنش باید در رویه صریح باشد. SET XACT_ABORT ON، TRY/CATCH، کنترل XACT_STATE و THROW الگوی پایه امن را می‌سازند. تراکنش فقط بخش اتمی را پوشش دهد و پیش از ورودی گرفتن از کاربر یا عملیات طولانی شروع نشود. دسترسی به جداول با ترتیب ثابت خطر Deadlock را کاهش می‌دهد.

SET NOCOUNT ON پیام‌های تعداد ردیف میانی را حذف می‌کند و در رویه‌های چندمرحله‌ای از ترافیک و سردرگمی مصرف‌کننده می‌کاهد. این تنظیم جای @@ROWCOUNT را از بین نمی‌برد؛ مقدار آن همچنان قابل استفاده است. اگر برنامه به پیام تعداد ردیف متکی است، قرارداد آن باید بازطراحی شود نه اینکه رفتار تصادفی حفظ گردد.

Parameter Sniffing ذاتاً خطا نیست؛ SQL Server از مقدار نخست برای ساخت Plan مناسب استفاده می‌کند. مشکل زمانی رخ می‌دهد که توزیع داده برای پارامترها بسیار متفاوت باشد. Query Store، Parameter Sensitive Plan در نسخه‌های جدید، بازنویسی Query یا RECOMPILE هدفمند گزینه‌های بررسی‌اند. پاک‌کردن کل Plan Cache راه‌حل حرفه‌ای نیست.

پایش رویه باید فراتر از میانگین Duration باشد. صدک‌های بالا، CPU، Logical Reads، Writes، Memory Grant، Spill، Wait، Blocking و نرخ خطا تصویر کامل‌تری می‌دهند. Query Store روند تاریخی و Planها را نگه می‌دارد و Extended Events رخداد دقیق را با سربار کنترل‌شده ثبت می‌کند.

آزمون رویه باید داده مرزی، NULL، رشته Unicode، حجم زیاد، هم‌زمانی، خطای مجوز و مسیر Rollback را پوشش دهد. تست واحد منطق کوچک را می‌سنجد و تست یکپارچه قرارداد واقعی جداول، ایندکس و تراکنش را تأیید می‌کند. داده آزمایشی باید قابل بازسازی و از اطلاعات شخصی واقعی جدا باشد.

نتیجه رویه برای مصرف‌کننده باید پایدار و کوچک بماند. SELECT * باعث تغییر خاموش قرارداد با افزودن ستون می‌شود و داده غیرضروری را منتقل می‌کند. ستون‌های لازم، نام مستعار ثابت و نوع داده مشخص انتخاب کنید. برای صفحه‌بندی، ترتیب قطعی و Tie-breaker یکتا ضروری است.

در پروژه‌های سازمانی، مالک هر رویه، هدف تجاری، SLA، جداول وابسته، سطح مجوز و داشبورد پایش باید مستند شود. این اطلاعات در حادثه عملیاتی از خواندن فوری صدها خط کد ارزشمندتر است. مستند فنی پس از هر تغییر مهم باید هم‌زمان با کد به‌روزرسانی شود.

بهینه‌سازی باید بر پایه Baseline انجام شود. ابتدا نسخه موجود را با پارامترهای نماینده اندازه بگیرید، سپس فقط یک تغییر هدفمند اعمال و دوباره مقایسه کنید. تفاوت محیط، کش و آمار را ثبت کنید. ادعای سریع‌ترشدن بدون عدد تکرارپذیر، معیار فنی قابل دفاعی نیست.

در نهایت، Stored Procedure زمانی انتخاب خوبی است که عملیات به نزدیکی داده، تراکنش اتمی، امنیت متمرکز یا استفاده مجدد میان چند مصرف‌کننده نیاز دارد. برای Query ساده و اختصاصی یک صفحه، رویه اضافی ممکن است هزینه‌ای بیش از سود داشته باشد. تصمیم معماری باید با مقیاس، تیم و چرخه تغییر سازگار باشد.

مقایسه دستورات اصلی

دستور CREATE PROCEDURE در SQL Server

CREATE PROCEDURE برای ایجاد یک رویه ذخیره‌شده جدید با قرارداد ورودی و خروجی مشخص است. در استفاده واقعی باید اثر آن بر قرارداد، دسترسی و انتشار سنجیده شود. مقاله مستقل این دستور Syntax، پارامترها، خطاها، Performance و ده سناریوی قابل اجرا را مرحله‌به‌مرحله ارائه می‌کند.

مطالعه آموزش تخصصی CREATE PROCEDURE با ده مثال عملی

دستور ALTER PROCEDURE در SQL Server

ALTER PROCEDURE برای تغییر تعریف یک رویه موجود بدون حذف هویت شیء است. در استفاده واقعی باید اثر آن بر قرارداد، دسترسی و انتشار سنجیده شود. مقاله مستقل این دستور Syntax، پارامترها، خطاها، Performance و ده سناریوی قابل اجرا را مرحله‌به‌مرحله ارائه می‌کند.

مطالعه آموزش تخصصی ALTER PROCEDURE با ده مثال عملی

دستور DROP PROCEDURE در SQL Server

DROP PROCEDURE برای حذف تعریف یک یا چند رویه ذخیره‌شده از پایگاه داده است. در استفاده واقعی باید اثر آن بر قرارداد، دسترسی و انتشار سنجیده شود. مقاله مستقل این دستور Syntax، پارامترها، خطاها، Performance و ده سناریوی قابل اجرا را مرحله‌به‌مرحله ارائه می‌کند.

مطالعه آموزش تخصصی DROP PROCEDURE با ده مثال عملی

دستور EXEC در SQL Server

EXEC برای اجرای رویه ذخیره‌شده یا یک Batch پویا با فرم کوتاه EXEC است. در استفاده واقعی باید اثر آن بر قرارداد، دسترسی و انتشار سنجیده شود. مقاله مستقل این دستور Syntax، پارامترها، خطاها، Performance و ده سناریوی قابل اجرا را مرحله‌به‌مرحله ارائه می‌کند.

مطالعه آموزش تخصصی EXEC با ده مثال عملی

دستور EXECUTE در SQL Server

EXECUTE برای اجرای رویه، رشته T-SQL یا تغییر کنترل‌شده زمینه اجرا با کلیدواژه کامل EXECUTE است. در استفاده واقعی باید اثر آن بر قرارداد، دسترسی و انتشار سنجیده شود. مقاله مستقل این دستور Syntax، پارامترها، خطاها، Performance و ده سناریوی قابل اجرا را مرحله‌به‌مرحله ارائه می‌کند.

مطالعه آموزش تخصصی EXECUTE با ده مثال عملی

شش مثال یکپارچه چرخه رویه

مثال 1: ساخت یک رویه پارامتری

در نخستین سناریو یک رویه ساده برای محاسبه مبلغ نهایی سفارش می‌سازیم. نوع پارامترها صریح است و نتیجه با یک نام ستون پایدار برگردانده می‌شود.

DROP PROCEDURE IF EXISTS dbo.usp_CalculateOrderTotal;
GO
CREATE PROCEDURE dbo.usp_CalculateOrderTotal
    @Quantity int,
    @UnitPrice decimal(18,2)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @Quantity * @UnitPrice AS TotalAmount;
END;
GO
EXEC dbo.usp_CalculateOrderTotal @Quantity = 3, @UnitPrice = 125000;
GO

نکته کاربردی: این الگو قرارداد ورودی و خروجی روشنی دارد و برای آزمون واحد مناسب است.

مثال 2: تغییر کنترل‌شده بدنه رویه

برای افزودن تخفیف، امضای رویه را توسعه می‌دهیم و نسخه جدید را با ALTER منتشر می‌کنیم. اسکریپت مهاجرت باید همراه نسخه قبلی و برنامه بازگشت نگهداری شود.

ALTER PROCEDURE dbo.usp_CalculateOrderTotal
    @Quantity int,
    @UnitPrice decimal(18,2),
    @DiscountPercent decimal(5,2) = 0
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Gross decimal(18,2) = @Quantity * @UnitPrice;
    SELECT @Gross * (1 - @DiscountPercent / 100.0) AS TotalAmount;
END;
GO
EXEC dbo.usp_CalculateOrderTotal 3, 125000, 10;
GO

نکته کاربردی: پارامتر اختیاری، سازگاری فراخوان‌های قدیمی را حفظ می‌کند.

مثال 3: اجرای رویه با پارامترهای نام‌دار

در کدهای عملی بهتر است پارامترها نام‌دار ارسال شوند تا جابه‌جایی ترتیب یا افزودن پارامتر اختیاری خوانایی فراخوان را از بین نبرد.

EXEC dbo.usp_CalculateOrderTotal
    @Quantity = 2,
    @UnitPrice = 98000,
    @DiscountPercent = 5;
GO

نکته کاربردی: نام‌گذاری پارامترها احتمال خطای انسانی را در اسکریپت‌های پشتیبانی کم می‌کند.

مثال 4: دریافت مقدار OUTPUT و Return Code

این مثال یک رویه مستقل می‌سازد که هم مقدار محاسبه‌شده را در پارامتر OUTPUT و هم وضعیت عملیات را با RETURN تحویل می‌دهد.

CREATE OR ALTER PROCEDURE dbo.usp_TryDivide
    @A decimal(18,2),
    @B decimal(18,2),
    @Result decimal(18,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    IF @B = 0 RETURN 1;
    SET @Result = @A / @B;
    RETURN 0;
END;
GO
DECLARE @Value decimal(18,2), @Status int;
EXEC @Status = dbo.usp_TryDivide 10, 4, @Value OUTPUT;
SELECT @Status AS StatusCode, @Value AS CalculatedValue;
GO

نکته کاربردی: Return Code برای وضعیت کوتاه و OUTPUT برای داده محاسباتی مناسب است.

مثال 5: SQL پویا با پارامتر امن

گاه ستون یا شرط در زمان اجرا تعیین می‌شود. بخش داده‌ای را با sp_executesql پارامتری می‌کنیم تا نوع داده حفظ و خطر تزریق کاهش یابد.

DECLARE @Sql nvarchar(max) = N'
SELECT @OrderId AS OrderId, @State AS OrderState;';
EXEC sys.sp_executesql
    @Sql,
    N'@OrderId int, @State nvarchar(20)',
    @OrderId = 501,
    @State = N'پرداخت‌شده';

نکته کاربردی: مقادیر کاربر را هرگز با الحاق رشته‌ای وارد متن دستور نکنید.

مثال 6: حذف ایمن رویه آزمایشی

در پایان چرخه آزمایشی، اشیای موقت را با شرط وجود حذف می‌کنیم تا اجرای دوباره اسکریپت خطا ندهد.

DROP PROCEDURE IF EXISTS dbo.usp_TryDivide;
DROP PROCEDURE IF EXISTS dbo.usp_CalculateOrderTotal;
GO
SELECT CASE
    WHEN OBJECT_ID(N'dbo.usp_TryDivide', N'P') IS NULL THEN N'حذف شد'
    ELSE N'هنوز وجود دارد'
END AS DropStatus;

نکته کاربردی: حذف باید پس از تحلیل وابستگی و در محیط تولید همراه نسخه پشتیبان و طرح بازگشت باشد.

چک‌لیست طراحی و انتشار

• نام Schema-qualified، مسئولیت واحد و قرارداد ورودی و خروجی مستند است.
• نوع و طول پارامتر با ستون مقصد سازگار و رفتار NULL مشخص است.
• مجوز برنامه فقط به عملیات لازم و ترجیحاً از طریق Role محدود شده است.
• Dynamic SQL پارامتری است و نام اشیا از Allowlist معتبر می‌آید.
• تراکنش کوتاه، XACT_ABORT، TRY/CATCH، Rollback و THROW آزموده شده‌اند.
• Baseline کارایی و Planهای Query Store پیش از تغییر ثبت شده‌اند.
• اسکریپت استقرار idempotent و Rollback از پیش آزمایش شده است.
• Smoke Test، تست قرارداد و پایش پس از انتشار مسئول مشخص دارند.

سؤالات متداول

۱. رویه ذخیره‌شده چیست؟

رویه ذخیره‌شده ماژول نام‌دار T-SQL در پایگاه داده است که پارامتر می‌گیرد، Query و تراکنش اجرا می‌کند و می‌تواند Result Set، OUTPUT یا Return Code برگرداند. ارزش آن در قرارداد پایدار، امنیت و استفاده مجدد است.

۲. آیا Stored Procedure جای ORM را می‌گیرد؟

خیر؛ این دو در لایه‌های متفاوت عمل می‌کنند. ORM نگاشت و دسترسی برنامه را ساده می‌کند و رویه برای عملیات حساس، گزارش پیچیده، امنیت متمرکز یا قرارداد پایگاه داده مفید است. معماری می‌تواند از هر دو هدفمند استفاده کند.

۳. رویه‌ها چگونه هزینه عملیاتی را کاهش می‌دهند؟

منطق متمرکز، مجوز هدفمند و پایش یک نقطه مشخص، خطا و دوباره‌کاری را کاهش می‌دهد. این مزیت فقط با کنترل نسخه، تست، مستندسازی و مسئولیت روشن تیمی محقق می‌شود.

۴. چه پروژه‌هایی به بازطراحی رویه نیاز دارند؟

سامانه‌هایی با Query کند، Deadlock، قرارداد خروجی ناپایدار، SQL پویا و دسترسی مستقیم گسترده نامزد بازطراحی‌اند. ارزیابی تخصصی باید ابتدا Baseline و ریشه مشکل را ثبت کند.

۵. تفاوت رویه و تابع SQL Server چیست؟

رویه آزادی بیشتری برای DML، تراکنش و چند Result Set دارد؛ تابع باید در محدودیت‌های مشخص یک مقدار یا جدول برگرداند و می‌تواند در Query استفاده شود. انتخاب بر پایه نوع قرارداد انجام می‌شود.

۶. برای سفارش پیاده‌سازی رویه چه خروجی‌هایی لازم است؟

تعریف نیاز، اسکریپت نسخه‌بندی‌شده، تست، Plan نمونه، مستند پارامتر و خروجی، مجوز، استقرار و Rollback مجموعه تحویل حرفه‌ای را می‌سازند. معیار پذیرش باید قابل اندازه‌گیری باشد.

۷. خطای رایج در چرخه Stored Procedure چیست؟

ویرایش مستقیم Production، پارامتر نامتناسب، SQL پویای الحاقی و تغییر Result Set بدون هماهنگی از خطاهای پرتکرارند. کنترل نسخه و تست قرارداد بخش بزرگی از ریسک را حذف می‌کنند.

۸. چگونه Performance رویه را بررسی کنیم؟

Query Store، Actual Execution Plan، STATISTICS IO/TIME و Extended Events را همراه داده نماینده به‌کار ببرید. چند مقدار پارامتر و وضعیت کش سرد و گرم را مقایسه کنید تا نتیجه تک‌اجرا گمراه‌کننده نباشد.

۹. مهم‌ترین Best Practice چیست؟

رویه را API پایگاه داده بدانید: Schema و قرارداد روشن، کمترین مجوز، مدیریت خطا، تراکنش کوتاه، اسکریپت idempotent، آزمون رگرسیون و مشاهده‌پذیری باید با هم طراحی شوند.

۱۰. این دستورات در Azure SQL پشتیبانی می‌شوند؟

هسته CREATE، ALTER، DROP و EXECUTE در Azure SQL پشتیبانی می‌شود، اما برخی گزینه‌های سطح سرور، Login یا اجرای راه دور تفاوت دارند. Compatibility Level و محدودیت محصول مقصد را بررسی کنید.

سؤالات مصاحبه

۱. چه زمانی Stored Procedure را به Query برنامه ترجیح می‌دهید؟

زمانی که تراکنش چندمرحله‌ای نزدیک داده، امنیت متمرکز، استفاده مجدد میان چند مصرف‌کننده یا گزارش پیچیده لازم باشد. پاسخ باید هزینه وابستگی و چرخه استقرار را نیز بسنجد.

۲. چگونه Parameter Sniffing را تشخیص می‌دهید؟

چند Plan و چند توزیع پارامتر را در Query Store و Actual Plan مقایسه می‌کنیم، تفاوت تخمین و ردیف واقعی را می‌سنجیم و پیش از انتخاب راه‌حل، آمار و ایندکس را کنترل می‌کنیم.

۳. ALTER چه مزیتی نسبت به DROP و CREATE دارد؟

object_id و مجوزهای مستقیم را حفظ می‌کند و انتشار را کم‌اختلال‌تر می‌سازد. با این حال، قرارداد خروجی و پارامترها ممکن است همچنان Breaking Change ایجاد کنند.

۴. الگوی مدیریت خطای مناسب چیست؟

SET XACT_ABORT ON، TRY/CATCH، تراکنش کوتاه، کنترل XACT_STATE، Rollback و THROW. ثبت Correlation ID و زمینه تجاری باید بدون بلعیدن خطای اصلی انجام شود.

۵. امنیت Dynamic SQL چگونه تأمین می‌شود؟

پارامتردهی داده با sp_executesql، Allowlist برای Identifier، QUOTENAME در محل درست، حداقل مجوز و تست ورودی مخرب. جایگزینی کوتیشن به‌تنهایی دفاع کافی نیست.

جمع‌بندی و مسیر مطالعه

چرخه حرفه‌ای Stored Procedure از طراحی قرارداد شروع می‌شود، با CREATE یا ALTER نسخه‌بندی‌شده ادامه می‌یابد، از طریق EXEC یا EXECUTE قابل مشاهده اجرا می‌شود و تنها پس از تحلیل مصرف و آماده‌سازی Rollback با DROP پایان می‌گیرد. کیفیت این چرخه به اندازه کیفیت Query داخلی بر پایداری سامانه اثر دارد.

• راهنمای کامل CREATE PROCEDURE
• راهنمای کامل ALTER PROCEDURE
• راهنمای کامل DROP PROCEDURE
• راهنمای کامل EXEC
• راهنمای کامل EXECUTE

برای اجرای Production، نمونه‌ها را با نام اشیا، نوع داده، نسخه SQL Server و سیاست امنیتی سازمان تطبیق دهید. ابتدا در محیط آزمایشی Baseline بگیرید، خطا و Rollback را عمداً امتحان کنید و سپس تغییر را با پایش فعال منتشر سازید.