مقدمه

دستور CREATE PROCEDURE یکی از اجزای اصلی کار با Stored Procedure در Microsoft SQL Server است. این مقاله از تعریف و Syntax آغاز می‌کند و سپس با ده مثال مستقل، مدیریت پارامتر، رفتار NULL، خطا، امنیت و کارایی را بررسی می‌کند. هدف این است که اسکریپت نهایی در SSMS قابل آزمون و برای پروژه واقعی قابل اقتباس باشد.

برای مشاهده جایگاه CREATE PROCEDURE در چرخه کامل ایجاد، تغییر، اجرا و حذف رویه‌ها، راهنمای جامع رویه‌های ذخیره‌شده در SQL Server را نیز مطالعه کنید. لینک حاضر بدون وابستگی به دامنه ساخته شده و با جابه‌جایی سایت همچنان معتبر می‌ماند.

تعریف و کاربرد CREATE PROCEDURE

CREATE PROCEDURE برای ایجاد یک رویه ذخیره‌شده جدید با قرارداد ورودی و خروجی مشخص استفاده می‌شود. در طراحی حرفه‌ای، این دستور فقط یک عبارت نحوی نیست؛ بخشی از قرارداد داده، مدل امنیت، چرخه انتشار و قابلیت مشاهده سامانه محسوب می‌شود.

اصل حرفه‌ای: پیش از استفاده از CREATE PROCEDURE، اثر آن بر قرارداد مصرف‌کننده، مجوزها، تراکنش و Planهای اجرایی را مشخص و قابل آزمون کنید.

Syntax استاندارد

CREATE [ OR ALTER ] PROCEDURE [schema_name.]procedure_name
    [ @parameter data_type [ = default ] [ OUTPUT ] [ ,...n ] ]
[ WITH procedure_option [ ,...n ] ]
AS
BEGIN
    sql_statement [ ; ] [ ...n ]
END;
GO

پارامترها و اجزای مهم

• schema_name مالک منطقی شیء است و بهتر است همیشه dbo یا Schema تخصصی صریح نوشته شود.
• procedure_name نام پایدار API پایگاه داده است و نباید پیشوند رزروشده sp_ داشته باشد.
• parameter ورودی یا خروجی تایپ‌شده است؛ مقادیر فارسی با nvarchar و N-prefixed ارسال شوند.
• WITH می‌تواند گزینه‌هایی مانند RECOMPILE یا EXECUTE AS را تعریف کند و باید هدفمند استفاده شود.

نوع خروجی و اثر دستور

CREATE PROCEDURE خودش Result Set برنمی‌گرداند؛ رویه ساخته‌شده می‌تواند Result Set، پارامتر OUTPUT و Return Code عدد صحیح تولید کند.

تحلیل فنی و معماری

رویه ذخیره‌شده مرز میان لایه کاربرد و منطق داده است. طراحی خوب فقط نوشتن چند SELECT در یک ماژول نیست؛ باید نام، مالکیت، ورودی، خروجی، رفتار NULL، تراکنش و خطا به‌عنوان قرارداد پایدار تعریف شوند. هر مصرف‌کننده باید بدون دانستن جزئیات داخلی بتواند نتیجه قابل پیش‌بینی بگیرد.

در ساخت رویه، نوع پارامتر را با نوع ستون مقصد یکسان انتخاب کنید. تفاوت varchar و nvarchar، طول نامتناسب یا تبدیل int به رشته می‌تواند Conversion ضمنی ایجاد کند و استفاده از ایندکس را مختل سازد. پارامترهای خیلی عمومی مانند nvarchar(max) فقط وقتی مناسب‌اند که واقعاً داده بزرگ لازم باشد.

امنیت یکی از مزیت‌های مهم رویه است. برنامه می‌تواند به‌جای مجوز مستقیم روی جدول‌ها فقط EXECUTE روی رویه داشته باشد. این مدل سطح حمله را کم می‌کند، اما SQL پویا، Ownership Chain و گزینه EXECUTE AS باید دقیق بررسی شوند؛ SQL پویای ناامن این مزیت را خنثی می‌کند.

برای عملیات چندمرحله‌ای، SET XACT_ABORT ON و TRY/CATCH را با تراکنش کوتاه ترکیب کنید. تراکنش نباید تعامل کاربر یا پردازش غیرضروری را در بر گیرد. در CATCH، XACT_STATE را کنترل، Rollback را تضمین و با THROW همان خطا را به لایه بالاتر منتقل کنید.

CREATE OR ALTER برای اسکریپت‌های استقرار تکرارپذیر بسیار مفید است، زیرا بدون DROP کردن شیء، نسخه مطلوب را برقرار می‌کند. با این حال، پشتیبانی نسخه مقصد را کنترل کنید. در محیط‌های قدیمی، ساخت Stub و سپس ALTER یا شرط OBJECT_ID راه جایگزین است.

پیش از انتشار، رویه را با داده کم، داده حجیم، NULL، مرز نوع داده و هم‌زمانی آزمایش کنید. Actual Execution Plan، Query Store و STATISTICS IO/TIME نشان می‌دهند که طراحی پارامتر و ایندکس واقعاً چه اثری دارد. موفق بودن Syntax به معنای مناسب بودن برای Production نیست.

ده مثال عملی و قابل اجرا

مثال 1: رویه بدون پارامتر

یک رویه خواندنی برای نمایش زمان سرور می‌سازیم تا ساختار پایه CREATE PROCEDURE روشن شود.

DROP PROCEDURE IF EXISTS dbo.usp_ServerClock;
GO
CREATE PROCEDURE dbo.usp_ServerClock
AS
BEGIN
    SET NOCOUNT ON;
    SELECT SYSDATETIME() AS ServerDateTime;
END;
GO
EXEC dbo.usp_ServerClock;

نکته کاربردی: قرار دادن SET NOCOUNT ON پیام‌های اضافی تعداد ردیف را حذف می‌کند.

مثال 2: پارامتر ورودی یونیکد

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

DROP PROCEDURE IF EXISTS dbo.usp_WelcomeUser;
GO
CREATE PROCEDURE dbo.usp_WelcomeUser @DisplayName nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CONCAT(N'سلام ', @DisplayName) AS MessageText;
END;
GO
EXEC dbo.usp_WelcomeUser @DisplayName = N'پرویز';

نکته کاربردی: برای داده فارسی، پارامتر nvarchar و مقدار N-prefixed ضروری است.

مثال 3: مقدار پیش‌فرض پارامتر

پارامتر صفحه اندازه پیش‌فرض دارد تا فراخوان ساده و در عین حال قابل تنظیم باشد.

DROP PROCEDURE IF EXISTS dbo.usp_PageSettings;
GO
CREATE PROCEDURE dbo.usp_PageSettings @PageNumber int, @PageSize int = 20
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @PageNumber AS PageNumber, @PageSize AS PageSize;
END;
GO
EXEC dbo.usp_PageSettings @PageNumber = 3;

نکته کاربردی: پارامترهای اجباری را پیش از پارامترهای دارای مقدار پیش‌فرض قرار دهید.

مثال 4: پارامتر OUTPUT

یک شناسه جدید را در متغیر خروجی قرار می‌دهیم تا مصرف‌کننده بدون Result Set آن را دریافت کند.

DROP PROCEDURE IF EXISTS dbo.usp_NewSequenceValue;
GO
CREATE PROCEDURE dbo.usp_NewSequenceValue @Seed int, @NextValue int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @NextValue = @Seed + 1;
END;
GO
DECLARE @N int;
EXEC dbo.usp_NewSequenceValue 40, @N OUTPUT;
SELECT @N AS NextValue;

نکته کاربردی: در فراخوان نیز واژه OUTPUT باید نوشته شود؛ وگرنه مقدار برنمی‌گردد.

مثال 5: Return Code برای وضعیت

رویه اعتبارسنجی با RETURN عددی، معتبر یا نامعتبر بودن ورودی را اعلام می‌کند.

DROP PROCEDURE IF EXISTS dbo.usp_ValidateQuantity;
GO
CREATE PROCEDURE dbo.usp_ValidateQuantity @Quantity int
AS
BEGIN
    IF @Quantity IS NULL OR @Quantity <= 0 RETURN 1;
    RETURN 0;
END;
GO
DECLARE @Code int;
EXEC @Code = dbo.usp_ValidateQuantity @Quantity = 5;
SELECT @Code AS ReturnCode;

نکته کاربردی: RETURN در رویه عدد صحیح برمی‌گرداند و جای Result Set داده‌ای نیست.

مثال 6: کار با جدول نمونه

یک جدول موقت محلی می‌سازیم و رویه با Dynamic SQL کنترل‌شده تعداد ردیف‌های آن را گزارش می‌کند؛ همه اجزا در یک اتصال قابل اجرا هستند.

CREATE TABLE #Orders(OrderId int, Amount decimal(18,2));
INSERT INTO #Orders VALUES (1, 150000), (2, 90000);
DROP PROCEDURE IF EXISTS dbo.usp_CountTempOrders;
GO
CREATE PROCEDURE dbo.usp_CountTempOrders
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COUNT(*) AS OrderCount FROM #Orders;
END;
GO
EXEC dbo.usp_CountTempOrders;
DROP TABLE #Orders;

نکته کاربردی: رویه به جدول موقت ایجادشده در همان Session دسترسی دارد، اما این وابستگی باید مستند باشد.

مثال 7: مدیریت NULL

ورودی NULL را با یک مقدار تجاری مشخص جایگزین می‌کنیم تا خروجی رویه قابل پیش‌بینی باشد.

DROP PROCEDURE IF EXISTS dbo.usp_NormalizeScore;
GO
CREATE PROCEDURE dbo.usp_NormalizeScore @Score decimal(5,2) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COALESCE(@Score, 0) AS NormalizedScore;
END;
GO
EXEC dbo.usp_NormalizeScore @Score = NULL;

نکته کاربردی: تصمیم درباره NULL بخشی از قرارداد رویه است و نباید به حدس مصرف‌کننده واگذار شود.

مثال 8: TRY/CATCH و تراکنش

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

DROP PROCEDURE IF EXISTS dbo.usp_TransactionalDemo;
GO
CREATE PROCEDURE dbo.usp_TransactionalDemo @Value int
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        IF @Value < 0 THROW 51000, N'مقدار منفی مجاز نیست.', 1;
        SELECT @Value AS AcceptedValue;
        COMMIT;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK;
        THROW;
    END CATCH;
END;
GO
EXEC dbo.usp_TransactionalDemo 12;

نکته کاربردی: THROW اطلاعات اصلی خطا را حفظ می‌کند و XACT_ABORT شکست‌های زمان اجرا را امن‌تر می‌سازد.

مثال 9: Schema و مجوز اجرا

رویه در Schema مشخص ساخته و فقط مجوز EXECUTE آن به یک نقش داده می‌شود؛ ساخت نقش نیز idempotent است.

IF DATABASE_PRINCIPAL_ID(N'AppExecutor') IS NULL
    CREATE ROLE AppExecutor;
GO
DROP PROCEDURE IF EXISTS dbo.usp_PublicStatus;
GO
CREATE PROCEDURE dbo.usp_PublicStatus
AS
BEGIN
    SET NOCOUNT ON;
    SELECT N'فعال' AS ServiceStatus;
END;
GO
GRANT EXECUTE ON dbo.usp_PublicStatus TO AppExecutor;

نکته کاربردی: مجوز را به Role بدهید، نه به تک‌تک کاربران، تا مدیریت دسترسی مقیاس‌پذیر بماند.

مثال 10: طراحی SARGable برای کارایی

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

DROP PROCEDURE IF EXISTS dbo.usp_DateRangePattern;
GO
CREATE PROCEDURE dbo.usp_DateRangePattern
    @FromDate date,
    @ToDate date
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @FromDate AS InclusiveStart,
           DATEADD(day, 1, @ToDate) AS ExclusiveEnd;
    -- الگوی شرط روی جدول واقعی:
    -- WHERE CreatedAt >= @FromDate
    --   AND CreatedAt < DATEADD(day, 1, @ToDate)
END;
GO
EXEC dbo.usp_DateRangePattern '2026-07-01', '2026-07-20';

نکته کاربردی: پارامتر هم‌نوع ستون و شرط بازه‌ای مستقیم، تبدیل ضمنی و Scan غیرضروری را کاهش می‌دهد.

خطاهای رایج

• نوشتن نام شیء بدون Schema در CREATE PROCEDURE باعث Resolution مبهم، خطای محیطی و گاهی استفاده کمتر مؤثر از Plan Cache می‌شود.
• یکسان ندانستن مقدار پیش‌فرض با NULL صریح می‌تواند منطق تجاری را تغییر دهد؛ هر دو مسیر را جداگانه آزمایش کنید.
• الحاق مستقیم ورودی کاربر به Dynamic SQL خطر تزریق، Conversion و تولید Planهای پراکنده ایجاد می‌کند.
• نادیده‌گرفتن مجوز، وابستگی یا شکل Result Set باعث شکست پس از استقرار می‌شود، حتی اگر اسکریپت DDL بدون خطا اجرا شده باشد.
• بلعیدن خطا در CATCH و برنگرداندن آن به فراخواننده، پایش و Rollback لایه کاربرد را غیرقابل اعتماد می‌کند.

ملاحظات Performance و بهینه‌سازی

برای سنجش CREATE PROCEDURE از حدس استفاده نکنید. Baseline شامل Duration، CPU، Logical Reads، تعداد اجرا و Waitها بسازید؛ سپس Actual Execution Plan و داده Query Store را پیش و پس از تغییر مقایسه کنید. تفاوت نوع پارامتر با ستون، Parameter Sniffing، آمار قدیمی و ایندکس نامناسب از علت‌های رایج افت کارایی‌اند.

• نام Schema و نوع پارامترها را دقیق و همسان با ستون مقصد نگه دارید.
• از SELECT * در قراردادهای پایدار دوری و ستون‌های لازم را صریح انتخاب کنید.
• تراکنش را کوتاه نگه دارید و دسترسی به اشیا را با ترتیب ثابت انجام دهید تا Deadlock کمتر شود.
• WITH RECOMPILE را راه‌حل پیش‌فرض ندانید؛ هزینه Compilation و تنوع پارامتر را با داده واقعی بسنجید.
• برای رگرسیون از Query Store و برای رخدادهای دقیق از Extended Events استفاده کنید.

Best Practices

• اسکریپت را idempotent و در کنترل نسخه نگهداری کنید.
• برای تغییرهای مخرب، برنامه Rollback و Health Check آماده داشته باشید.
• کمترین سطح مجوز را از طریق Role و GRANT هدفمند پیاده کنید.
• ورودی، خروجی، NULL، خطا و سازگاری نسخه را در تست خودکار پوشش دهید.
• نام‌گذاری تجاری پایدار و توضیح مسئولیت رویه را در مستند فنی ثبت کنید.
• کد Production را با داده نماینده و حجم نزدیک به واقعیت آزمایش کنید.

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

۱. CREATE PROCEDURE در SQL Server دقیقاً چه کاری انجام می‌دهد؟

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

۲. از چه نسخه‌ای می‌توان از قابلیت‌های جدید CREATE PROCEDURE استفاده کرد؟

هسته دستور در نسخه‌های قدیمی SQL Server نیز وجود دارد، اما گزینه‌هایی مانند CREATE OR ALTER یا DROP IF EXISTS به نسخه وابسته‌اند. پیش از استقرار، Compatibility Level و مستندات همان نسخه را بررسی کنید.

۳. آیا استفاده از CREATE PROCEDURE هزینه نگهداری سامانه را کاهش می‌دهد؟

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

۴. چه زمانی برای طراحی مبتنی بر CREATE PROCEDURE به مشاوره نیاز داریم؟

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

۵. تفاوت کاربرد CREATE PROCEDURE با اجرای مستقیم Query چیست؟

CREATE PROCEDURE یک واحد نام‌دار و قابل کنترل در چرخه استقرار ایجاد می‌کند، در حالی که Query مستقیم معمولاً پراکنده‌تر است. انتخاب صحیح به نیاز استفاده مجدد، امنیت، پارامتردهی، کش طرح اجرا و مسئولیت تیم‌ها بستگی دارد.

۶. چگونه می‌توان پیاده‌سازی CREATE PROCEDURE را برای پروژه سفارش داد؟

ابتدا ورودی‌ها، خروجی‌ها، SLA، سطح دسترسی و سناریوهای خطا مستند می‌شوند؛ سپس نمونه قابل آزمون، اسکریپت استقرار و معیار پذیرش تهیه می‌شود. این روش تحویل پروژه را قابل سنجش و پشتیبانی را ساده‌تر می‌کند.

۷. رایج‌ترین خطای مرتبط با CREATE PROCEDURE چیست؟

رایج‌ترین خطا نادیده‌گرفتن زمینه اجرایی، Schema یا وابستگی‌هاست. به‌ویژه کنترل نوع پارامتر، Schema، مجوز و وابستگی ضروری است. ثبت متن کامل خطا، شماره خط و نام رویه در CATCH، یافتن علت را بسیار سریع‌تر می‌کند.

۸. CREATE PROCEDURE چه اثری بر Performance دارد؟

خود دستور فقط بخشی از مسئله است؛ کیفیت Queryهای داخل رویه، نوع پارامتر، آمار، ایندکس و هم‌زمانی تعیین‌کننده‌اند. Query Store، Actual Execution Plan و Extended Events ابزارهای مناسب سنجش پیش و پس از تغییر هستند.

۹. بهترین روش استفاده از CREATE PROCEDURE چیست؟

اسکریپت idempotent، نام Schema-qualified، کمترین سطح مجوز، قرارداد پایدار، SET NOCOUNT ON، مدیریت خطا و آزمون بازگشت را هم‌زمان رعایت کنید. هر تغییر باید در کنترل نسخه و فرایند انتشار ثبت شود.

۱۰. آیا CREATE PROCEDURE با Azure SQL و نسخه‌های جدید سازگار است؟

بخش اصلی در SQL Server و Azure SQL Database قابل استفاده است، ولی برخی گزینه‌های امنیتی یا اجرای راه‌دور میان محصولات تفاوت دارند. اسکریپت را روی موتور و سطح سازگاری مقصد آزمایش کنید و به فرض سازگاری کامل اکتفا نکنید.

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

۱. تفاوت Result Set، OUTPUT و RETURN هنگام CREATE PROCEDURE چیست؟

Result Set داده جدولی، OUTPUT مقدار پارامتری و RETURN کد وضعیت int است. پاسخ حرفه‌ای باید درباره قرارداد، نوع داده و نحوه دریافت هر سه در فراخواننده توضیح دهد.

۲. چرا Schema-qualified بودن نام در CREATE PROCEDURE مهم است؟

ابهام نام را از بین می‌برد، امنیت و خوانایی را بهتر می‌کند و Resolution شیء و استفاده مجدد از Plan را قابل پیش‌بینی‌تر می‌سازد.

۳. چگونه SQL Injection را در اجرای پویا مهار می‌کنید؟

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

۴. پس از تغییر رویه چه شاخص‌هایی را مقایسه می‌کنید؟

Duration، CPU، Logical Reads، Cardinality Estimate، Memory Grant، Spill، Waitها و Planهای Query Store باید با Baseline و چند توزیع پارامتر مقایسه شوند.

۵. طرح بازگشت مناسب چیست؟

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

چک‌لیست نهایی

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

جمع‌بندی

CREATE PROCEDURE زمانی ارزش واقعی ایجاد می‌کند که همراه قرارداد روشن، امنیت حداقلی، مدیریت خطا و سنجش کارایی استفاده شود. مثال‌های این مقاله الگوهای پایه تا حرفه‌ای را پوشش دادند؛ آن‌ها را با Schema، نوع داده و سیاست انتشار پروژه خود تطبیق دهید و پیش از Production در محیط آزمایشی اجرا کنید.

برای مرور همه دستورات این مجموعه، به مقاله مادر Stored Procedures در SQL Server بازگردید.