PHP SQL Server: Calling Stored Procedures

Summary: in this tutorial, you will learn how to call a stored procedure from SQL Server using PHP.

This tutorial begins where performing a transaction using PHP tutorial left off.

Creating a stored procedure

Step 1. Launch the SQL Server Management Studio (SSMS) and connect to the BookStore database.

Step 2. Execute the following statement to create a new stored procedure that retrieves authors by birth date between start and end dates:

CREATE PROCEDURE GetAuthorsByBirthDate
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        AuthorID,
        FirstName,
        LastName,
        BirthDate
    FROM 
        Authors
    WHERE 
        BirthDate BETWEEN @StartDate AND @EndDate
    ORDER BY 
        BirthDate;
END;
GOCode language: SQL (Structured Query Language) (sql)

Calling the stored procedure from PHP

Define a method findAuthorsByBirthDate in the AuthorDB class to call the GetAuthorsByBirthDate stored procedure:

function findAuthorsByBirthDate($startDate, $endDate)
{
    $authors = [];

    try {
        $stmt = $this->conn->prepare("EXEC GetAuthorsByBirthDate :startDate, :endDate");
        $stmt->bindParam(':startDate', $startDate);
        $stmt->bindParam(':endDate', $endDate);

        $stmt->execute();

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $authors[] = new Author(
                $row['FirstName'],
                $row['LastName'],
                $row['BirthDate'],
                $row['AuthorID']
            );
        }
    } catch (PDOException $e) {
        throw new Exception("Error fetching authors: " . $e->getMessage());
    }

    return $authors;
}Code language: PHP (php)

How it works.

Step 1. Define a public method findAuthorsByBirthDate that finds authors with the birth dates between start and end dates:

function findAuthorsByBirthDate($startDate, $endDate)Code language: PHP (php)

Step 2. Initialize an array to store the Author objects created from the data of the Authors table:

$authors = [];Code language: PHP (php)

Step 3. Create a prepared statement that executes a call to the GetAuthorsByBirthDate stored procedure.

$stmt = $this->conn->prepare("EXEC GetAuthorsByBirthDate :startDate, :endDate");Code language: PHP (php)

The :startDate and :endDate are placeholders for query parameters, which help prevent SQL injections.

Step 4. Bind the values for the :startDate and :endDate parameters:

$stmt->bindParam(':startDate', $startDate);
$stmt->bindParam(':endDate', $endDate);Code language: PHP (php)

Step 5. Execute the prepared statement by calling the execute() method:

$stmt->execute();Code language: PHP (php)

Step 6. Fetch rows, create an Author for each row, and add it to the $authors array:

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
   $authors[] = new Author(
                $row['FirstName'],
                $row['LastName'],
                $row['BirthDate'],
                $row['AuthorID']
            );
}Code language: PHP (php)

Step 7. Return the $authors array:

return $authorsCode language: PHP (php)

Running the app

Modify the index.php file to call the findAuthorsByBirthDate method:

<?php

require_once 'authordb.php';

// Connect to the SQL Server
$conn = require_once 'connect.php';

$authorDB = new AuthorDB($conn);

// Find authors between birth dates
$authors = $authorDB->findAuthorsByBirthDate('1980-01-01', '1986-12-31');

foreach ($authors as $author) {
    echo $author->getFirstName() . ' ' .
         $author->getLastName() . ' : ' .
         $author->getBirthDate() . '<br>';
}Code language: PHP (php)

Output:

Donna Bailey : 1980-11-20
Lisa Savage : 1981-04-13
David Hunter : 1983-08-01
Karen Simmons : 1986-06-19Code language: PHP (php)

How it works.

First, load the authordb.php file to use the AuthorDB class.

require_once 'authordb.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 AuthorDB object:

$authorDB = new AuthorDB($conn);Code language: PHP (php)

Fourth, call the findAuthorsByBirthDate() method to retrieve authors whose birth dates are between '1980-01-01' and '1986-12-31':

$authors = $authorDB->findAuthorsByBirthDate('1980-01-01', '1986-12-31');Code language: PHP (php)

Finally, display author’s information:

foreach ($authors as $author) {
    echo $author->getFirstName() . ' ' .
         $author->getLastName() . ' : ' .
         $author->getBirthDate() . '<br>';
}Code language: PHP (php)

Summary

• Execute the EXEC statement to call a stored procedure from SQL Server in PHP.