On my Windows box (home2k), I use Microsoft SQL Server query analyzer to create the following stored procedure on pubs database (I will use this stored procedure on Sybase SQL Server with pubs2 database in my next article):
CREATE PROC sp_GetBooksByPrice
@minPrice money,
@maxPrice money,
@lowestPricedBook varchar(100) OUTPUT,
@highestPricedBook varchar(100) OUTPUT
AS
DECLARE @realminPrice money, @realmaxPrice money, @totalBooks int
SELECT @realminPrice = min(price) FROM titles WHERE price >=@minPrice
SELECT @realmaxPrice = max(price) FROM titles WHERE price <=@maxPrice
SELECT @lowestPricedBook =title FROM titles WHERE price = @realminPrice
SELECT @highestPricedBook =title FROM titles WHERE price = @realmaxPrice
SELECT @totalBooks = COUNT(title) FROM titles WHERE price >= @minPrice AND price <= @maxPrice
RETURN @totalBooks
GO
On the Red Hat side, use an editor to create the following file called sp_test.php:
$myServer = "home2k";
$myUser = "sa";
$myPass = "";
$myDB = "pubs";
$s = mssql_connect($myServer, $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer");
mssql_select_db($myDB, $s)
or die("Couldn't open database $myDB");
$proc = mssql_init("sp_GetBooksByPrice", $s);
$minPrice = 2.00;
$maxPrice = 20.00;
$lowestPricedBook = "";
$highestPricedBook = "";
$numBooks = 0;
// Bind the parameters
mssql_bind($proc, "@minPrice", $minPrice, SQLFLT8);
mssql_bind($proc, "@maxPrice", $maxPrice, SQLFLT8);
mssql_bind($proc, "@lowestPricedBook", $lowestPricedBook, SQLVARCHAR, TRUE, FALSE,100);
mssql_bind($proc, "@highestPricedBook", $highestPricedBook, SQLVARCHAR, TRUE, FALSE,100);
// Bind the return value
mssql_bind($proc, "RETVAL", $numBooks, SQLINT2);
mssql_execute($proc);
mssql_free_statement ($proc);
mssql_close($s);
echo "
There were $numBooks Books returned.
";echo "The lowest price book was: $lowestPricedBook.
";
echo "The highest price book was: $highestPricedBook.";
?>
Save the file in /usr/local/Apache2/htdocs, open your browser, and input http://localhost/sp_test.php in address bar. It is useful when you debug your PHP code with MS SQL Server.
