رویه‌های ذخیره شده Stored Procedures

CREATE PROCEDURE نام_پروسیجر
(
    @پارامتر1 نوع_داده,
    @پارامتر2 نوع_داده
)
AS
BEGIN
    -- دستورات SQL
END

CREATE PROCEDURE GetEmployeeDetails
(
    @EmployeeID INT
)
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END

EXEC GetEmployeeDetails @EmployeeID = 1;

CREATE PROCEDURE InsertCustomer
(
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Email NVARCHAR(100)
)
AS
BEGIN
    INSERT INTO Customers (FirstName, LastName, Email)
    VALUES (@FirstName, @LastName, @Email);
END

EXEC InsertCustomer 
    @FirstName = 'Ali',
    @LastName = 'Rezaei',
    @Email = 'ali.rezaei@example.com';

CREATE PROCEDURE GetTotalCustomers
(
    @TotalCustomers INT OUTPUT
)
AS
BEGIN
    SELECT @TotalCustomers = COUNT(*) FROM Customers;
END

DECLARE @Total INT;
EXEC GetTotalCustomers @TotalCustomers = @Total OUTPUT;
PRINT 'Total Customers: ' + CAST(@Total AS NVARCHAR(10));

CREATE PROCEDURE GetCustomerByEmail
(
    @Email NVARCHAR(100)
)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @Email)
    BEGIN
        SELECT * FROM Customers WHERE Email = @Email;
    END
    ELSE
    BEGIN
        PRINT 'Customer with email ' + @Email + ' not found.';
    END
END

EXEC GetCustomerByEmail @Email = 'ali.rezaei@example.com';

CREATE PROCEDURE InsertOrder
(
    @CustomerID INT,
    @ProductID INT,
    @Quantity INT,
    @OrderDate DATE
)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        -- درج سفارش جدید در جدول Orders
        INSERT INTO Orders (CustomerID, OrderDate)
        VALUES (@CustomerID, @OrderDate);

        -- دریافت OrderID ایجاد شده
        DECLARE @OrderID INT = SCOPE_IDENTITY();

        -- درج جزئیات سفارش در جدول OrderDetails
        INSERT INTO OrderDetails (OrderID, ProductID, Quantity)
        VALUES (@OrderID, @ProductID, @Quantity);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT 'An error occurred. Transaction rolled back.';
    END CATCH
END

EXEC InsertOrder 
    @CustomerID = 1,
    @ProductID = 101,
    @Quantity = 2,
    @OrderDate = '2025-02-14';

CREATE PROCEDURE UpdateEmptyEmails
AS
BEGIN
    DECLARE @CustomerID INT;
    DECLARE @Email NVARCHAR(100);

    -- تعریف کرسر برای پیمایش داده‌ها
    DECLARE CustomerCursor CURSOR FOR
    SELECT CustomerID, Email FROM Customers;

    OPEN CustomerCursor;
    FETCH NEXT FROM CustomerCursor INTO @CustomerID, @Email;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @Email IS NULL OR @Email = ''
        BEGIN
            UPDATE Customers
            SET Email = 'no-email@example.com'
            WHERE CustomerID = @CustomerID;
        END

        FETCH NEXT FROM CustomerCursor INTO @CustomerID, @Email;
    END

    CLOSE CustomerCursor;
    DEALLOCATE CustomerCursor;
END

EXEC UpdateEmptyEmails;

CREATE PROCEDURE GetCustomers
(
    @FirstName NVARCHAR(50) = NULL,
    @LastName NVARCHAR(50) = NULL
)
AS
BEGIN
    SELECT * FROM Customers
    WHERE (FirstName = @FirstName OR @FirstName IS NULL)
      AND (LastName = @LastName OR @LastName IS NULL);
END

-- همه مشتریان
EXEC GetCustomers;

-- مشتریان با نام "Ali"
EXEC GetCustomers @FirstName = 'Ali';

-- مشتریان با نام خانوادگی "Ramezani"
EXEC GetCustomers @LastName = 'Ramezani';