PHP Programming MSSQL Stored Procedures

ตัวอย่างการสร้าง Stored Procedures

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\">&nbsp;</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\">&nbsp;Office Data </font></strong></td>";
print "          <td width=\"200\"><strong><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">&nbsp;Total Print Jobs</font></strong></td>";
print "          <td width=\"200\"><strong><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">&nbsp;Number of Pages</font></strong></td>";
print "          <td width=\"200\"><strong><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">&nbsp;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\">&nbsp;".$officeName."</font></td>";
    print "          <td width=\"200\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">&nbsp;".$pJobs."</font></td>";
    print "          <td width=\"200\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">&nbsp;".$pages."</font></td>";
    print "          <td width=\"200\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">&nbsp;".$cost."</font></td>";
    print "        </tr>";
  }
} // End While Loop

// Close Table     print "      </table></td>";
print "  </tr>";
print "</table>";