مقدمه

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

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

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

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

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

Syntax استاندارد

DROP PROCEDURE [ IF EXISTS ]
    [ schema_name. ] procedure_name [ ,...n ];

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

• IF EXISTS اجرای تکراری را امن‌تر می‌کند و در نسخه‌های پشتیبانی‌شده خطای نبود شیء را حذف می‌کند.
• schema_name باید صریح باشد تا شیء هم‌نام در Schema دیگر هدف قرار نگیرد.
• procedure_name می‌تواند فهرستی از چند رویه باشد، ولی اثر و وابستگی هر مورد باید پیش‌تر ارزیابی شود.

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

DROP PROCEDURE خروجی داده‌ای ندارد؛ متادیتا و تعریف شیء را حذف می‌کند و فراخوان‌های بعدی با خطای نبود رویه روبه‌رو می‌شوند.

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

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

پیش از DROP، تعریف مورد تأیید را در کنترل نسخه و نسخه پشتیبان نگه دارید. OBJECT_DEFINITION برای مشاهده سریع مفید است، اما منبع حقیقت نباید کاتالوگ Production باشد. Commit متناظر، درخواست تغییر و دلیل حذف امکان ممیزی و بازیابی را فراهم می‌کنند.

DROP IF EXISTS اسکریپت را idempotent می‌کند، ولی خطای منطقی را پنهان نکنید. اگر وجود رویه پیش‌شرط مهاجرت است، نبود آن ممکن است نشانه اجرای نسخه اشتباه باشد و باید THROW ایجاد کند. انتخاب میان شرط نرم و Guard سخت به قرارداد انتشار بستگی دارد.

SQL Server بسیاری از عملیات DDL را در تراکنش پشتیبانی می‌کند. می‌توان DROP را همراه تغییرهای مرتبط اتمی کرد و در خطا Rollback نمود. با این حال، قفل Schema و رشد Log دلیل خوبی است که تراکنش حذف کوتاه و دقیق بماند.

پس از حذف، Smoke Test باید همه مسیرهای شناخته‌شده را اجرا کند و پایش خطای Could not find stored procedure فعال باشد. بازگشت سریع معمولاً اجرای نسخه قبلی CREATE/ALTER است؛ به همین دلیل اسکریپت بازگشت باید پیش از شروع عملیات آماده و آزموده باشد.

از DROP برای درمان عجولانه مشکلات Plan Cache یا Performance استفاده نکنید. حذف و ساخت مجدد می‌تواند مجوزها و متادیتا را تغییر دهد و علت اصلی را پنهان سازد. Query Store، sp_recompile یا اصلاح هدفمند Query ابزارهای مناسب‌تری برای تحلیل کارایی هستند.

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

مثال 1: حذف یک رویه موجود

رویه آزمایشی ساخته و با DROP PROCEDURE حذف می‌شود؛ کنترل OBJECT_ID وضعیت نهایی را نشان می‌دهد.

CREATE OR ALTER PROCEDURE dbo.usp_DropDemo01 AS SELECT 1 AS V;
GO
DROP PROCEDURE dbo.usp_DropDemo01;
GO
SELECT OBJECT_ID(N'dbo.usp_DropDemo01', N'P') AS ProcedureObjectId;

نکته کاربردی: نام Schema را همیشه بنویسید تا شیء اشتباه هدف قرار نگیرد.

مثال 2: حذف شرطی با IF EXISTS

قابلیت IF EXISTS اسکریپت را idempotent می‌کند و اجرای چندباره خطا نمی‌دهد.

CREATE OR ALTER PROCEDURE dbo.usp_DropDemo02 AS SELECT 2 AS V;
GO
DROP PROCEDURE IF EXISTS dbo.usp_DropDemo02;
GO
DROP PROCEDURE IF EXISTS dbo.usp_DropDemo02;

نکته کاربردی: برای SQL Serverهای قدیمی‌تر از کنترل OBJECT_ID استفاده کنید.

مثال 3: حذف چند رویه در یک دستور

دو شیء مستقل می‌سازیم و هر دو را در یک DROP حذف می‌کنیم.

CREATE OR ALTER PROCEDURE dbo.usp_DropDemo03A AS SELECT N'A' AS V;
GO
CREATE OR ALTER PROCEDURE dbo.usp_DropDemo03B AS SELECT N'B' AS V;
GO
DROP PROCEDURE dbo.usp_DropDemo03A, dbo.usp_DropDemo03B;
GO

نکته کاربردی: اگر یکی از نام‌ها نامعتبر باشد، رفتار Batch را پیش از استفاده در انتشار بررسی کنید.

مثال 4: روش سازگار با نسخه قدیمی

به‌جای IF EXISTS، وجود شیء با نوع P کنترل می‌شود تا View یا شیء دیگری هم‌نام اشتباه حذف نشود.

CREATE OR ALTER PROCEDURE dbo.usp_DropDemo04 AS SELECT 4 AS V;
GO
IF OBJECT_ID(N'dbo.usp_DropDemo04', N'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_DropDemo04;
GO

نکته کاربردی: پارامتر نوع N'P' کنترل را دقیق‌تر می‌کند.

مثال 5: حذف داخل تراکنش و بازگشت

DDL در SQL Server تراکنشی است؛ رویه را حذف و سپس ROLLBACK می‌کنیم تا شیء بازگردد.

CREATE OR ALTER PROCEDURE dbo.usp_DropDemo05 AS SELECT 5 AS V;
GO
BEGIN TRANSACTION;
DROP PROCEDURE dbo.usp_DropDemo05;
ROLLBACK;
GO
EXEC dbo.usp_DropDemo05;

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

مثال 6: کنترل وابستگی پیش از حذف

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

CREATE OR ALTER PROCEDURE dbo.usp_DropDemo06 AS SELECT 6 AS V;
GO
SELECT referencing_schema_name, referencing_entity_name
FROM sys.dm_sql_referencing_entities(N'dbo.usp_DropDemo06', N'OBJECT');
GO
DROP PROCEDURE IF EXISTS dbo.usp_DropDemo06;

نکته کاربردی: SQL پویا ممکن است در کاتالوگ وابستگی دیده نشود؛ جست‌وجوی کد و آزمون یکپارچه نیز لازم است.

مثال 7: حفظ تعریف پیش از حذف

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

CREATE OR ALTER PROCEDURE dbo.usp_DropDemo07 AS SELECT 7 AS V;
GO
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.usp_DropDemo07')) AS DefinitionBeforeDrop;
GO
DROP PROCEDURE IF EXISTS dbo.usp_DropDemo07;

نکته کاربردی: نسخه اصلی باید در Git نگهداری شود؛ خروجی OBJECT_DEFINITION فقط کنترل کمکی است.

مثال 8: رفتار نام Schema اشتباه

به‌جای اجرای DROP خطرناک، ابتدا دو Schema را کنترل می‌کنیم و فقط هدف دقیق را حذف می‌کنیم.

CREATE OR ALTER PROCEDURE dbo.usp_DropDemo08 AS SELECT 8 AS V;
GO
SELECT OBJECT_ID(N'dbo.usp_DropDemo08', N'P') AS DboObject,
       OBJECT_ID(N'audit.usp_DropDemo08', N'P') AS AuditObject;
GO
DROP PROCEDURE IF EXISTS dbo.usp_DropDemo08;

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

مثال 9: حذف در TRY/CATCH

حذف را در مدیریت خطا قرار می‌دهیم تا پیام دقیق و امکان Rollback برای عملیات چندمرحله‌ای فراهم باشد.

CREATE OR ALTER PROCEDURE dbo.usp_DropDemo09 AS SELECT 9 AS V;
GO
BEGIN TRY
    BEGIN TRANSACTION;
    DROP PROCEDURE dbo.usp_DropDemo09;
    COMMIT;
    SELECT N'حذف موفق' AS DropResult;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    THROW;
END CATCH;

نکته کاربردی: خطای اصلی را با THROW دوباره ارسال کنید تا سامانه پایش آن را دریافت کند.

مثال 10: حذف و پاک‌سازی Plan Cache همان شیء

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

CREATE OR ALTER PROCEDURE dbo.usp_DropDemo10 AS SELECT 10 AS V;
GO
EXEC dbo.usp_DropDemo10;
SELECT object_id, name FROM sys.procedures
WHERE object_id = OBJECT_ID(N'dbo.usp_DropDemo10');
GO
DROP PROCEDURE IF EXISTS dbo.usp_DropDemo10;
GO
SELECT COUNT(*) AS RemainingObjects FROM sys.procedures
WHERE name = N'usp_DropDemo10';

نکته کاربردی: DROP درمان مشکل Performance نیست؛ ابتدا علت طرح نامناسب را با Query Store تحلیل کنید.

خطاهای رایج

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

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

برای سنجش DROP 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 را با داده نماینده و حجم نزدیک به واقعیت آزمایش کنید.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

۲. چرا Schema-qualified بودن نام در DROP 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 و نسخه پشتیبان DROP PROCEDURE بررسی شد.
• Schema، نام و انواع پارامتر صریح‌اند.
• رفتار NULL و مقادیر مرزی تست شده است.
• مجوزها حداقلی و قابل ممیزی‌اند.
• خطا و تراکنش مسیر موفق و ناموفق را پوشش می‌دهند.
• Baseline و نتیجه آزمون Performance ثبت شده است.
• اسکریپت انتشار و بازگشت در کنترل نسخه قرار دارد.

جمع‌بندی

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

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