Stored Procedure

T-SQL | Parameters، OUTPUT، RETURN و EXEC

Stored Procedure مجموعه نام‌گذاری‌شده Statementهای T-SQL است که Parameter می‌گیرد، Result Set تولید می‌کند و می‌تواند عملیات چندمرحله‌ای و Transactional را نزدیک داده اجرا کند.

متن این فصل با رویکرد مرجع فنی و قابل استفاده در پروژه واقعی تنظیم شده است؛ مثال‌ها در امتداد مدل StoreDb نوشته شده‌اند و اصطلاحات اصلی SQL Server و T-SQL به‌صورت یکدست به‌کار می‌روند.

1. مفاهیم و قراردادهای اصلی

Stored Procedure مجموعه نام‌گذاری‌شده Statementهای T-SQL است که Parameter می‌گیرد، Result Set تولید می‌کند و می‌تواند عملیات چندمرحله‌ای و Transactional را نزدیک داده اجرا کند.

در طراحی پایگاه داده، Syntax تنها بخشی از مسئله است. نوع داده، Constraint، الگوی دسترسی، Transaction و هزینه اجرای Query باید هم‌زمان با نیاز Domain در نظر گرفته شوند.

2. ساختار و Syntax پایه

SET NOCOUNT ON در Procedureهای عملیاتی رایج است. Parameterها Type دقیق دارند و Ruleهای مهم در صورت امکان با Constraint نیز محافظت می‌شوند.

CREATE OR ALTER PROCEDURE dbo.usp_GetProductById
    @ProductId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Id, Name, Sku, Price, Stock
    FROM dbo.Products
    WHERE Id = @ProductId;
END;
GO

3. سناریوی StoreDb

Procedure کاهش Stock تنها زمانی Update می‌کند که موجودی کافی باشد و با @@ROWCOUNT نبود Product یا کمبود موجودی را تشخیص می‌دهد.

نمونه‌های این فصل بر مدل فروشگاهی StoreDb بنا شده‌اند تا ارتباط مفاهیم میان فصل‌ها حفظ شود و Queryها در یک Context یکپارچه قابل بررسی باشند.

CREATE OR ALTER PROCEDURE dbo.usp_DecreaseProductStock
    @ProductId int,
    @Quantity int
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.Products
    SET Stock = Stock - @Quantity
    WHERE Id = @ProductId
      AND Stock >= @Quantity;

    IF @@ROWCOUNT = 0
        THROW 50031, 'Product not found or insufficient stock.', 1;
END;
GO

4. اصول طراحی و Performance

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

• Parameter نام‌دار هنگام EXEC خوانایی را افزایش می‌دهد.
• Procedure Contract خروجی پایدار داشته باشد.
• CREATE OR ALTER برای Deployment مناسب است.
• Permission EXECUTE می‌تواند DML مستقیم را محدود کند.

5. خطاهای رایج و نکات ایمنی

دستورهای SQL می‌توانند مستقیماً داده و ساختار را تغییر دهند؛ بنابراین بازبینی Context، تعداد Rowهای هدف و اثر Transaction قبل از اجرای تغییرات اهمیت عملی دارد.

• Prefix sp_ برای Procedure کاربری.
• Dynamic SQL ناامن با Concatenation.
• Transaction بدون TRY/CATCH در عملیات چندمرحله‌ای.
• RETURN برای برگرداندن داده غیر int.

6. چک‌لیست نهایی فصل

• Input و Output Parameter درست تعریف شده‌اند.
• Result Set Contract مشخص است.
• خطا با THROW مدیریت می‌شود.
• Procedure قابل Deployment تکرارپذیر است.

7. جمع‌بندی

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