Stored Procedures on PHP and Microsoft SQL Server

As an example, to call the sample stored procedure defined above, you would use the following lines of code:

$oQuery = new SqlCommand('GetCustomerList');  
$oQuery->addParam('StoreId', $_GET['StoreId'], 'int');  
$oQuery->addParam('CustomerType', $_GET['CustomerType'], 'varchar', 50);  
 
// Assume you have an open PEAR database connection ($pearDB)  
$arrResults = $oQuery->getAll($pearDB);

That seems a little more readable than dynamically building the SQL string to call this procedure yourself, doesn't it? And, because you define each parameter to the stored procedure with the actual database field type, the SqlCommand class is able to perform variable “typing” for security -- a strong method of preventing SQL Injection Attacks. For example:

• int values are forced to integers using the built-in PHP function intval()
• datetime values are validated as dates using a regular expression filter
• varchar values are trimmed to meet the maximum size requirements if necessary, and filtered to escape single quotes

What about return values and output parameters?

Right, I did mention those, didn't I? Well, the ability to retrieve return values is enabled by default with the SqlCommand class (as mentioned above). So, in our example, if you wanted the actual return value of the GetCustomerList procedure, all you would need to do is call $oQuery->getReturnValue().

Output parameters are almost as easy to retrieve, but do require an extra line of code when configuring your SqlCommand object. Let's assume we have a different stored procedure that actually uses some output parameters, like the following:

GetStoreCustomerCount @StoreId int, @CustomerCount int OUTPUT

In this case, you can modify your configuration of the SqlCommand class, and easily obtain the value of this new output parameter, as in the following:

$oQuery = new SqlCommand('GetStoreCustomerCount');  
$oQuery->addParam('StoreId', $_GET['StoreId'], 'int');  
$oQuery->addParam('CustomerCount', null, 'int');  
 
// One extra method call is required to configure a parameter for output  
$oQuery->setParamDirection('CustomerCount', true);  
 
// Assume you have an open PEAR database connection ($pearDB)  
$arrResults = $oQuery->getAll($pearDB);  
 
// Now retrieve the value of your output parameter  
$count = $oQuery->getOutputValue('CustomerCount');

That's all there is to it. You can configure any number of output parameters, and retrieve their values (as well as the return value of the stored procedure), plus any query results your stored procedures happen to yield, via SELECT statements.

What’s really going on behind the scenes?

If you’ve ever executed a stored procedure yourself directly through the Microsoft Query Analyzer, you’ll know that return values and output parameters are not returned automatically there, either -- you have to do a little extra work. For instance, in order to find the return value for a stored procedure, you need to write something along these lines:

DECLARE @res int  
EXEC @res = GetCustomerList 1, ‘SomeType’  
SELECT @res

The query result for SELECT @res contains the return value of your stored procedure call. Retrieving the values of output parameters is somewhat similar, typically like this:

DECLARE @out_value int  
EXEC GetStoreCustomerCount 1, @out_value OUTPUT  
SELECT @out_value

Every return value or output parameter that you want to track must first be declared appropriately (of the correct data type), then included in your actual stored procedure call. The values themselves can then be returned using a SELECT statement following the execution. The SqlCommand class automatically wraps your database queries with these types of statements to provide the stated functionality of each.

Conclusion

Stored procedures are a powerful tool that should be utilized extensively when you develop software that interacts with SQL Server. Although you can use them directly in PHP -- similar to dynamically constructed query statements -- your code may benefit from using a more structured approach. The SqlCommand class should provide this structure, and in the process help improve the simplicity, maintainability and security of your project.

Don't forget to download the code from this article here.