วันจันทร์ที่ 21 พฤษภาคม พ.ศ. 2555

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_showallSQLINT4); mssql_bind($stmt"@Location"$SQL_officeSQLVARCHARFALSEFALSE20); mssql_bind($stmt"@Range"$SQL_rangeSQLINT4);// 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>";  

ไม่มีความคิดเห็น:

แสดงความคิดเห็น