นำมาฝาก : เรียก storeprocedure บน php
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
--------------------------
<?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 "<h2>There were $numBooks Books returned.</h2>"; echo "The lowest price book was: <b>$lowestPricedBook</b>.<br>"; echo "The highest price book was: <b>$highestPricedBook</b>."; ?>
|