CREATE PROC usp_ShowOfficeData
@ShowAll INT,
@Location VARCHAR(20),
@Range INT
AS
--Declare the variables
DECLARE @Select AS VARCHAR(1000)
--Build the Select Clause
IF @ShowAll = 0 --User wants only the top 5 offices, the default setting
BEGIN
SELECT @Select =
'SELECT TOP 6
SUBSTRING(fldPrintServer, 1, 3) AS Office,
COUNT(*) AS [Total Print Jobs],
SUM(fldPageCount) AS Pages,
''$'' + (CONVERT(VARCHAR(15),CAST( ((SUM(fldpagecount)) * (SELECT AVG(fldPageCost) FROM tblPrinters)) AS INT) ) ) AS Cost
FROM tblPrints '
END
IF @ShowAll = 1 --User wants to see all of the offices
BEGIN
SELECT @Select =
'SELECT
SUBSTRING(fldPrintServer, 1, 3) AS Office,
COUNT(*) AS [Total Print Jobs],
SUM(fldPageCount) AS Pages,
''$'' + (CONVERT(VARCHAR(15),CAST( ((SUM(fldpagecount)) * (SELECT AVG(fldPageCost) FROM tblPrinters)) AS INT) ) ) AS Cost
FROM tblPrints '
END
--Build the Where clause
IF @location != 'All' --ALL locations is default
BEGIN
IF (@location != '') OR (@location IS NOT NULL) --Checks for a bad input
BEGIN
SELECT @Select = @Select +
'WHERE fldPrintServer LIKE ''' + @location + '%'' '
END
END
IF @range = 0 --0 is show MTD which is default
BEGIN
IF @location != 'ALL' --Does the WHERE clause already contain info
BEGIN
SELECT @select = @select +
'AND DATEPART(Month, fldDateTime) = DATEPART(Month, GETDATE()) '
END
ELSE
BEGIN
SELECT @select = @select +
'WHERE DATEPART(Month, fldDateTime) = DATEPART(Month, GETDATE()) '
END
END
ELSE
IF @range = 1 --1 is show YTD data
BEGIN
IF @location != 'ALL' --Does the WHERE clause already contain info
BEGIN
SELECT @select = @select +
'AND DATEPART(Year, fldDateTime) = DATEPART(Year, GETDATE()) '
END
ELSE
BEGIN
SELECT @select = @select +
'WHERE DATEPART(Year, fldDateTime) = DATEPART(Year, GETDATE()) '
END
END
ELSE --2 is show year 2002 data
BEGIN
IF @location != 'ALL' --Does the WHERE clause already contain info
BEGIN
SELECT @select = @select +
'AND DATEPART(Year, fldDateTime) = 2002 '
END
ELSE
BEGIN
SELECT @select = @select +
'WHERE DATEPART(Year, fldDateTime) = 2002 '
END
END
--Build Group and Order By statements
SELECT @Select = @Select +
'GROUP BY (SUBSTRING(fldprintserver, 1, 3))
WITH ROLLUP
ORDER BY Pages DESC'
--Execute the statement and return the results
EXEC (@select)
GO
ตัวอย่างการใช้ PHP เรียกใช้งาน Stored Procedures
PHP Code:
// Link Identifier $conn = mssql_pconnect("server", "username", "password");
if(! $conn){
print "A connect to the SQL Server was not made.";
}// Select Database Name $db = mssql_select_db("PLog", $conn);
if(! $db){
print "The database was not found.";
}
// Create Querry for Stored Procedure In Code $stmt = mssql_init("usp_ShowOfficeData",$conn);$SQL_office = $office; $SQL_range = $range; $SQL_showall = 1;// Bind Peramaters To Send To Stored Proc. mssql_bind($stmt, "@ShowAll", $SQL_showall, SQLINT4); mssql_bind($stmt, "@Location", $SQL_office, SQLVARCHAR, FALSE, FALSE, 20); mssql_bind($stmt, "@Range", $SQL_range, SQLINT4);// Execute SQL Statement & Capture Results $result = mssql_execute($stmt);// Fetch Return Data And Place Into Array For Access $row0 = mssql_fetch_array($result);
// Print Firm MTD Totals In Table print "<table width=\"800\" border=\"0\" cellspacing=\"0\" cellpadding=\"1\">";
print " <tr>";
print " <td><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"><strong>Firm Totals: </strong>".$rangeDisplay."</font></td>";
print " </tr>";
print "</table>";
print "<table width=\"800\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";
print " <tr>";
print " <td width=\"20\"> </td>";
print " <td width=\"260\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">Print Jobs: ".$row0["Total Print Jobs"]."</font></td>";
print " <td width=\"260\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">Total Printed Pages: ".$row0["Pages"]."</font></td>";
print " <td width=\"260\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">Total Cost: ".$row0["Cost"]."</font></td>";
print " </tr>";
print "</table>";
print "<br><br>";
// Print Each Office's MTD Totals In A Table print "<table width=\"800\" border=\"0\" cellspacing=\"0\" cellpadding=\"1\">";
print " <tr>";
print " <td bgcolor=\"#000000\"><table width=\"800\" border=\"0\" cellspacing=\"1\" cellpadding=\"0\">";
print " <tr bgcolor=\"#99CC66\">";
print " <td width=\"200\"><strong><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"> Office Data </font></strong></td>";
print " <td width=\"200\"><strong><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"> Total Print Jobs</font></strong></td>";
print " <td width=\"200\"><strong><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"> Number of Pages</font></strong></td>";
print " <td width=\"200\"><strong><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"> Total Cost </font></strong></td>";
print " </tr>";
// Place Office Print Data Into The Main Table By Rows while($row1 = mssql_fetch_array($result)){
// Assign Variables
$officeName = $row1["Office"];
$pJobs = $row1["Total Print Jobs"];
$pages = $row1["Pages"];
$cost = $row1["Cost"];
if($officeName == NULL){}
else{
// Print Variables in Table
print " <tr bgcolor=\"#FFFFFF\">";
print " <td width=\"200\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"> ".$officeName."</font></td>";
print " <td width=\"200\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"> ".$pJobs."</font></td>";
print " <td width=\"200\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"> ".$pages."</font></td>";
print " <td width=\"200\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"> ".$cost."</font></td>";
print " </tr>";
}
} // End While Loop
// Close Table print " </table></td>";
print " </tr>";
print "</table>"; 