Executing Stored Procedures from PHP Example

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