GetDepartments

Stored Procedure
CREATE PROC GetDepartments
AS
    SELECT * FROM Department;
GO
Code PHP
<?php
    $pdo = new PDO('sqlsrv:Server=.;Database=hr', "sa", "123");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //$stmt = $pdo->query('EXEC GetDepartments');
    $stmt = $pdo->query('{CALL GetDepartments()}');
    $a = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $stmt->closeCursor();
    unset($pdo);
    echo "<pre>";
    print_r($a);
?>

GetDepartmentById

Stored Procedure
CREATE PROC GetDepartmentById(@id INT)
AS
BEGIN
    SELECT DepartmentId, DepartmentName FROM Department WHERE DepartmentId = @id;
END
Code PHP
<?php 
    $pdo = new PDO('sqlsrv:Server=.;database=hr', 'sa', '123');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //$stmt = $pdo->prepare('EXEC GetDepartmentById :id');
    $stmt = $pdo->prepare('{CALL GetDepartmentById(:id)}');
    $stmt->execute(['id' => 1]);
    $a = $stmt->fetch(PDO::FETCH_ASSOC);
    $stmt->closeCursor();
    print_r($a);
    unset($pd);
?>

GetDepartmentById Multiple Rowset

Stored Procedure
CREATE PROC GetDepartmentById(@id INT)
AS
BEGIN
    SELECT DepartmentId, DepartmentName FROM Department WHERE DepartmentId = @id;
    SELECT EmployeeId, FullName, Gender, DateOfBirth, Salary 
        FROM Employee WHERE DepartmentId = @id;
END
Code PHP
<?php 
    $pdo = new PDO('sqlsrv:Server=.;database=hr', 'sa', '123');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //$stmt = $pdo->prepare('EXEC GetDepartmentById :id');
    $stmt = $pdo->prepare('{CALL GetDepartmentById(:id)}');
    $stmt->execute(['id' => 1]);
    $a = $stmt->fetch(PDO::FETCH_ASSOC);
    if($stmt->nextRowset()){
        $b = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    $stmt->closeCursor();
    unset($pd);
    echo "<pre>";
    print_r($a);
    print_r($b);
?>

Add Department

Stored Procedure
CREATE PROC AddDepartment(@name NVARCHAR(128))
AS
    INSERT INTO Department(DepartmentName) VALUES(@name);
GO
Code PHP
<?php 
    $pdo = new PDO('sqlsrv:Server=.;database=hr', 'sa', '123');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //$stmt = $pdo->prepare('EXEC AddDepartment :name');
    $stmt = $pdo->prepare('{CALL AddDepartment(:name)}');
    echo $stmt->execute(['name'=>'Finance']);
    $stmt->closeCursor();
    unset($pdo);
?>

Add Deparment With Output Parameter

Stored Procedure
CREATE PROC AddDepartment(@id INT OUT, @name NVARCHAR(128))
AS
BEGIN
    INSERT INTO Department(DepartmentName) VALUES(@name);
    SET @id = SCOPE_IDENTITY();
END
Code PHP
<?php 
    $pdo = new PDO('sqlsrv:Server=.;database=hr', 'sa', '123');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //$stmt = $pdo->prepare('EXEC AddDepartment :id, :name');
    $stmt = $pdo->prepare('{CALL AddDepartment(:id, :name)}');
    $id = 0;
    $stmt->bindParam('id', $id, PDO::PARAM_INT, 4);
    $name = 'Development';
    $stmt->bindParam('name', $name);
    $stmt->execute();
    $stmt->closeCursor();
    unset($pdo);
    echo 'id: ' . $id;
?>

Method Add Deparment return Value

Stored Procedure
CREATE PROC AddDepartment(@name NVARCHAR(128))
AS
BEGIN
    INSERT INTO Department(DepartmentName) VALUES(@name);
    RETURN SCOPE_IDENTITY();
END
GO
Stored Procedure
<?php 
    $pdo = new PDO('sqlsrv:Server=.;database=hr', 'sa', '123');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $pdo->prepare('{? = CALL AddDepartment(?)}');
    $id = 0;
    $stmt->bindParam(1, $id, PDO::PARAM_INT, 4);
    $name = 'SEO';
    $stmt->bindParam(2, $name);
    $stmt->execute();
    $stmt->closeCursor();
    unset($pdo);
    echo 'id: ' . $id;
?>