Azure SQL Database and SQL Server programming with PHP: Stored procedures, Transactions, and UDFs
This blog will teach you how to write Transactions, Stored Procedures and User Defined Functions (UDF’s) with Azure SQL Database in PHP. This allows you to write application logic that can be shipped and executed directly on the database
Stored Procedures
A stored procedure is an already written SQL statement that is saved in the database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure. When you put this SQL statement in a stored procedure, you can then run the stored procedure from the database’s command environment, using the exec command.
An example is: exec usp_displayallusers. The name of the stored procedure is “usp_displayallusers”, and “exec” tells SQL Server to execute the code in the stored procedure. (Note: “usp_” in front of the stored procedure name is used to designate this stored procedure as a user-created stored procedure.) The code inside the stored procedure can be something as simple as:
SELECT * FROM USERLIST
This “select” statement will return all data in the USERLIST table. Just save the query and run it when you need to.
Example: Write a simple stored procedure
This Stored Procedure does a SELECT STATEMENT
Step 1: Setup the connection:
<?php
$serverName = "tcp:yourserver.database.windows.net,1433";
$connectionOptions = array("Database"=>"yourdatabase",
"Uid"=>"yourusername", "PWD"=>"yourpassword");
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);
Step 2: Create the Stored Procedure
$tsql = "CREATE PROCEDURE sp_GetCompanies22 AS BEGIN SELECT [CompanyName] FROM SalesLT.Customer END";
$storedProc = sqlsrv_query($conn, $tsql);
if($storedProc == FALSE){
echo "Error creating Stored Procedure";
die(FormatErrors( sqlsrv_errors()));
}
sqlsrv_free_stmt($storedProc);
Step 3 : Execute the Stored Procedure
You can execute the Stored Procedure using the exec statement. We will print the results of the select statement to the screen.
$tsql = "exec sp_GETCompanies22";
//Executes the query
$getProducts = sqlsrv_query($conn, $tsql);
//Error handling
if ($getProducts == FALSE){
echo "Error executing Stored Procedure";
die(FormatErrors(sqlsrv_errors()));
}
$productCount = 0;
$ctr = 0;
?>
<h1> First 10 results are after executing the stored procedure: </h1>
<?php
while($row = sqlsrv_fetch_array($getProducts, SQLSRV_FETCH_ASSOC)){
//Printing only the first 10 results
if($ctr>9)
break;
$ctr++;
echo($row['CompanyName']);
echo("<br/>");
$productCount++;
}
sqlsrv_free_stmt($getProducts);
Step 4: Drop the Stored Procedure
$tsql = "DROP PROCEDURE sp_GETCompanies22";
$storedProc = sqlsrv_query($conn, $tsql);
if($storedProc == FALSE)
{
echo "Error dropping Stored Procedure";
die(FormatErrors( sqlsrv_errors()));
}
sqlsrv_free_stmt($storedProc);
Transactions
A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.
A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.
Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction.
Example: Write a simple transaction
Step 1: Begin transaction
We use the sqlsrv_begin_transaction() function to start the transaction
if (sqlsrv_begin_transaction($conn) == FALSE)
{
echo "Error opening connection";
die(FormatErrors(sqlsrv_errors()));
}
Step 2: Write the queries for the transaction
$tsql1 = "INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID,OrderQty,ProductID,UnitPrice) VALUES (71774, 22, 709, 33)";
$stmt1 = sqlsrv_query($conn, $tsql1);
/* Set up and execute the second query. */
$tsql2 = "UPDATE SalesLT.SalesOrderDetail SET OrderQty = (OrderQty + 1) WHERE ProductID = 709";
$stmt2 = sqlsrv_query( $conn, $tsql2);
Step 3: Commit/Rollback the transaction
We do this by using the sqlsrv_commit() or the sqlsrv_rollback() function
/* If both queries were successful, commit the transaction. */
/* Otherwise, rollback the transaction. */
if($stmt1 && $stmt2)
{
sqlsrv_commit($conn);
echo "Transaction was committed.\n";
}
else
{
sqlsrv_rollback($conn);
echo "Transaction was rolled back.\n";
}
/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt1);
sqlsrv_free_stmt( $stmt2);
?>
User-defined functions
Like functions in programming languages, SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.
The benefits of using user-defined functions in Azure SQL Database are:
- They allow modular programming.
You can create the function once, store it in the database, and call it any number of times in your program. User-defined functions can be modified independently of the program source code.
- They allow faster execution.
Similar to stored procedures, Transact-SQL user-defined functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times.
CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.
Example: Write a simple UDF
Step 1: Create UDF
We create a complex UDF that returns a
$tsql1 = "IF OBJECT_ID(N'dbo.ifGetTotalItems', N'IF') IS NOT NULL DROP FUNCTION dbo.ifGetTotalItems;";
$getProducts = sqlsrv_query($conn, $tsql1);
//Error handling
if ($getProducts == FALSE)
{
echo "Error deleting the UDF";
die(FormatErrors(sqlsrv_errors()));
}
$tsql1 = "CREATE FUNCTION dbo.ifGetTotalItems (@OrderID INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN (SELECT SUM(OrderQty) AS TotalItems FROM SalesLT.SalesOrderDetail WHERE SalesOrderID = @OrderID GROUP BY SalesOrderID);";
$getProducts = sqlsrv_query($conn, $tsql1);
//Error handling
if ($getProducts == FALSE)
{
echo "Error creating the UDF";
die(FormatErrors(sqlsrv_errors()));
}
Step 2: Use the UDF in a query
$tsql1 = "SELECT s.SalesOrderID, s.OrderDate, s.CustomerID, f.TotalItems FROM SalesLT.SalesOrderHeader s CROSS APPLY dbo.ifGetTotalItems(s.SalesOrderID) f ORDER BY SalesOrderID;";
$getProducts = sqlsrv_query($conn, $tsql1);
//Error handling
if ($getProducts == FALSE)
{
echo "Error executing the UDF";
die(FormatErrors(sqlsrv_errors()));
}
$productCount = 0;
$ctr = 0;
?>
<h1> First 10 results are after executing a query that uses the UDF: </h1>
<?php
echo "SalesOrderID CustomerID TotalItems";
echo("<br/>");
while($row = sqlsrv_fetch_array($getProducts, SQLSRV_FETCH_ASSOC))
{
//Printing only the top 10 results
if($ctr>9)
break;
$ctr++;
echo $row['SalesOrderID'] . str_repeat(' ', 13) . $row['CustomerID'] . str_repeat(' ', 11) . $row['TotalItems'];
echo("<br/>");
$productCount++;
}
sqlsrv_free_stmt($getProducts);