This tutorial begins where calling a stored procedure from PHP tutorial left off.
Summary: in this tutorial, you will learn how to call a stored procedure with OUTPUT parameters in PHP.
Creating a stored procedure
Step 1. Launch Microsoft SQL Server Management Studio (SSMS) and connect to the SQL Server.
Step 2. Execute the following statement to create a new stored procedure named createOrder with an output parameter:
CREATE PROCEDURE CreateOrder
@CustomerId INT,
@BookId INT,
@Quantity INT,
@Price DECIMAL(18, 2),
@OrderDate DATE,
@OrderId INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
-- Start a transaction
BEGIN TRANSACTION;
-- Check inventory
DECLARE @CurrentQty INT;
SELECT @CurrentQty = Qty FROM Inventories WHERE BookId = @BookId;
IF @CurrentQty IS NULL OR @CurrentQty < @Quantity
BEGIN
RAISERROR('Insufficient inventory', 16, 1);
ROLLBACK TRANSACTION;
RETURN;
END
-- Insert into Orders table
INSERT INTO Orders (OrderDate, CustomerId, TotalAmount)
VALUES (@OrderDate, @CustomerId, @Price * @Quantity);
-- Get the generated OrderId
SET @OrderId = SCOPE_IDENTITY();
-- Insert into OrderDetails table
INSERT INTO OrderDetails (OrderId, BookId, Quantity, Price)
VALUES (@OrderId, @BookId, @Quantity, @Price);
-- Update the inventory
UPDATE Inventories
SET Qty = Qty - @Quantity
WHERE BookId = @BookId;
-- Commit the transaction
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Handle errors and rollback the transaction
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
-- Return error information
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
ENDCode language: SQL (Structured Query Language) (sql)Calling stored procedures with OUTPUT parameters
Remove all methods in the OrderDB class and redefine the create() method that calls the CreateOrder stored procedure:
<?php
class OrderDB
{
public function __construct(private \PDO $conn)
{
$this->conn = $conn;
}
public function create($customerId, $bookId, $quantity, $price, $orderDate)
{
try {
// Prepare the stored procedure call
$sql = "DECLARE @OrderId INT;
EXEC CreateOrder :customerId, :bookId, :quantity, :price, :orderDate, @OrderId OUTPUT;
SELECT @OrderId AS OrderId;";
$stmt = $this->conn->prepare($sql);
// Bind parameters
$stmt->bindParam(':customerId', $customerId, PDO::PARAM_INT);
$stmt->bindParam(':bookId', $bookId, PDO::PARAM_INT);
$stmt->bindParam(':quantity', $quantity, PDO::PARAM_INT);
$stmt->bindParam(':price', $price, PDO::PARAM_STR); // Using PDO::PARAM_STR for DECIMAL
$stmt->bindParam(':orderDate', $orderDate, PDO::PARAM_STR);
// Execute the stored procedure
$stmt->execute();
// Fetch the order ID from the result set
$result = $stmt->fetch(PDO::FETCH_ASSOC);
// Return the order ID
return $result['OrderId'];
} catch (PDOException $e) {
throw new Exception("Failed to create order: " . $e->getMessage());
}
}
}Code language: PHP (php)How it works.
Step 1. Define a public method create that creates a new order based on a customer Id, book Id, order quantity, book price, and order date:
public function create($customerId, $bookId, $quantity, $price, $orderDate)Code language: PHP (php)Step 2. Construct statements that call the CreateOrder stored procedure and returned the generated order Id:
$sql = "DECLARE @OrderId INT;
EXEC CreateOrder :customerId, :bookId, :quantity, :price, :orderDate, @OrderId OUTPUT;
SELECT @OrderId AS OrderId;";Code language: PHP (php)Step 3. Bind the values to the query parameters:
$stmt->bindParam(':customerId', $customerId, PDO::PARAM_INT);
$stmt->bindParam(':bookId', $bookId, PDO::PARAM_INT);
$stmt->bindParam(':quantity', $quantity, PDO::PARAM_INT);
$stmt->bindParam(':price', $price, PDO::PARAM_STR); // Using PDO::PARAM_STR for DECIMAL
$stmt->bindParam(':orderDate', $orderDate, PDO::PARAM_STR);Code language: PHP (php)Step 4. Execute the prepared statement:
$stmt->execute();Code language: PHP (php)Step 5. Retrieve the order id:
$result = $stmt->fetch(PDO::FETCH_ASSOC);Code language: PHP (php)Step 6. Return the order id from the result set:
return $result['OrderId'];Code language: PHP (php)Step 7. Throw a new Exception if it occurs when creating the order:
throw new Exception("Failed to create order: " . $e->getMessage());Code language: PHP (php)Running the app
Step 1. Modify the index.php that uses the createOrder method of OrderDB object:
<?php
require_once 'orderdb.php';
// Connect to the SQL Server
$conn = require_once 'connect.php';
// Create an instance of the OrderDB class
$orderDB = new OrderDB($conn);
// Prepare data
$customerId = 1;
$bookId = 1;
$quantity = 10;
$price = 9.99;
$orderDate = '2024-07-31';
$orderId = $orderDB->create(
$customerId,
$bookId,
$quantity,
$price,
$orderDate
);
echo "New Order ID: " . $orderId;Code language: PHP (php)How it works.
First, load the orderdb.php file to use the OrderDB class.
require_once 'orderdb.php';Code language: PHP (php)Second, connect to the SQL Server and assign the returned PDO object to the $conn variable:
$conn = require_once 'connect.php';Code language: PHP (php)Third, create a new OrderDB object with the PDO object:
$orderDB = new OrderDB($conn);Code language: PHP (php)Fourth, initialize the order data:
$customerId = 1;
$bookId = 1;
$quantity = 10;
$price = 9.99;
$orderDate = '2024-07-31';Code language: PHP (php)Fourth, call the create() method to create a new order:
$orderId = $orderDB->create(
$customerId,
$bookId,
$quantity,
$price,
$orderDate
);Code language: PHP (php)Finally, display the order id:
echo "New Order ID: " . $orderId;Code language: PHP (php)Step 2. Run the index.php file on the web browser, the output will look like this:
New Order ID: 2Code language: PHP (php)Summary
- Execute a prepared statement to call a stored procedure with
OUTPUTparameters.
