Executing SQL Server Stored Procedures With PHP - Capturing output parameters and return values

(Page 5 of 6 )

It's just as easy to use output parameters and capture return values from stored procedures. Run the following code in query analyzer to create a stored procedure that accepts both input and output values and also returns a result:

USE Northwind

GO

CREATE PROC sp_GetNumProdsByPrice

@minPrice MONEY,

@maxPrice MONEY,

@lowestPricedProduct VARCHAR(40) OUTPUT,

@highestPricedProduct VARCHAR(40) OUTPUT

AS

SELECT @lowestPricedProduct = (SELECT TOP 1 ProductName

FROM Products

WHERE UnitPrice >= @minPrice

ORDER BY UnitPrice ASC)

SELECT @highestPricedProduct = (SELECT TOP 1 ProductName

FROM Products

WHERE UnitPrice <= @maxPrice

ORDER BY UnitPrice DESC)

RETURN (SELECT COUNT(*) FROM Products WHERE UnitPrice >= @minPrice AND UnitPrice <= @maxPrice)

Our stored procedure works with the products table of the Northwind database and is called sp_GetNumProdsByPrice. It accepts two input and two output parameters. The input parameters are the minimum and maximum price of the item (UnitPrice) to match. The output parameters will contain the names of the products whose UnitPrice field was close to @minPrice and @maxPrice respectively.

Now, onto the PHP code. Create a new file called prodsbyprice.php and enter the following code into it:

<?php

$myServer = "localhost";

$myUser = "sa";

$myPass = "";

$myDB = "Northwind";

$s = @mssql_connect($myServer, $myUser, $myPass)

or die("Couldn't connect to SQL Server on $myServer");

$d = @mssql_select_db($myDB, $s)

or die("Couldn't open database $myDB");

$query = mssql_init("sp_GetNumProdsByPrice", $s);

$minPrice = 0.00;

$maxPrice = 35.00;

$lowProd = "";

$highProd = "";

$numProds = 0;

// Bind the parameters

mssql_bind($query, "@minPrice", $minPrice, SQLFLT8);

mssql_bind($query, "@maxPrice", $maxPrice, SQLFLT8);

mssql_bind($query, "@lowestPricedProduct", &$lowProd, SQLVARCHAR, TRUE, FALSE, 40);

mssql_bind($query, "@highestPricedProduct", &$highProd, SQLVARCHAR, TRUE, FALSE, 40);

// Bind the return value

mssql_bind($query, "RETVAL", &$numProds, SQLINT2);

mssql_execute($query);

echo "<h2>There were $numProds products returned.</h2>";

echo "The lowest priced product was $lowProd.<br>";

echo "The highest priced product was $highProd.";

?>

We start of by defining a number of variables that will be used with our calls to mssql_bind:

$minPrice = 0.00;

$maxPrice = 35.00;

$lowProd = "";

$highProd = "";

$numProds = 0;

[Note] When you are using mssql_bind to setup parameters for stored procedures, you cannot explicitly specify the value for that parameter. You must pass in a variable that contains the value instead. [End Note]

// Bind the parameters

mssql_bind($query, "@minPrice", $minPrice, SQLFLT8);

mssql_bind($query, "@maxPrice", $maxPrice, SQLFLT8);

We then create the input parameters @minPrice and @maxPrice with values taken from the $minPrice amd $maxPrice variables.

mssql_bind($query, "@lowestPricedProduct", &$lowProd, SQLVARCHAR, TRUE, FALSE, 40);

mssql_bind($query, "@highestPricedProduct", &$highProd, SQLVARCHAR, TRUE, FALSE, 40);

For our output parameters, we have specified values for every parameter that the mssql_bind function accepts. Each of our output parameters needs to accept a value back from the stored procedure, so we pass $lowProd and $highProd by reference not value (the ampersand signifies the reference). They will contain the values of the output parameters from the stored procedure after it's executed.

Our parameters will hold the names of products, so we declare them as SQLVARCHAR types. They are output parameters, so we pass in TRUE for the is_output parameter, FALSE for the is_null parameter, and 40 for the maxlen parameter.

In our stored procedure, we return the total number of products whose UnitPrice field is between the values of the @minPrice and @maxPrice input parameters:

RETURN (SELECT COUNT(*) FROM Products WHERE UnitPrice >= @minPrice AND UnitPrice <= @maxPrice)

We access this return value as "RETVAL", returning its value to the $numProds variable, which we pass to mssql_bind by reference:

// Bind the return value

mssql_bind($query, "RETVAL", &$numProds, SQLINT2);

When mssql_execute is called, the stored procedure executes and the values for the output parameters are returned as well as the RETVAL return value. We then output the results to the browser with the echo command:

mssql_execute($query);

echo "<h2>There were $numProds products returned.</h2>";

echo "The lowest priced product was $lowProd.<br>";

echo "The highest priced product was $highProd.";

Here's how prodsbyprice.php looks in my browser:



If you change the values of $minPrice and $maxPrice in prodsbyprice.php, then obviously different products will be returned. When I changed $minPrice to 14.50 and $maxPrice to 350.00, I got the following output:



We can also run our stored procedure directly against the Northwind database in query anazlyer with this code:

USE Northwind

GO

DECLARE @lowProd VARCHAR(40)

DECLARE @highProd VARCHAR(40)

EXEC sp_GetNumProdsByPrice 0, 35.50, @lowProd OUTPUT, @highProd OUTPUT

print 'Lowest priced product is ' + @lowProd

print 'Highest priced product is ' + @highProd

Next: Conclusion >>
 

More PHP Articles
More By Joe O'Donnell

Comments On: Executing SQL Server Stored Procedures With PHP

>>> Be the FIRST to comment on this article!