Stored Procedures (dynamic Order By Web Assistant Wizard)

1
Stored Procedures(dynamic Order By Web
Assistant Wizard)

• Kevin Penny, MMCP
• kevin_at_hotgigs.com

2
Advantages

• Pre-compilation faster execution
• Less Data transferred across network
• EXAMPLE
• exec sp_getproducts 12,234
• vs.
• SELECT Employees.Country, Employees.LastName,
  Employees.FirstName, Orders.ShippedDate,
• Orders.OrderID, "Order Subtotals".Subtotal AS
  SaleAmount
• FROM Employees INNER JOIN
• (Orders INNER JOIN "Order Subtotals" ON
  Orders.OrderID "Order Subtotals".OrderID)
• ON Employees.EmployeeID Orders.EmployeeID
• Re-usable execution plans used by the database
  server for each proc
• More Secure (for inputted data)

3
Disadvantages

• More skill required to write
• May find it to be less flexible than simple
  CFQUERY execution.
• More planning needed for proper implementation
  (not always a bad thing!)

4
Dynamic Order by Statement

• You need to define all possible columns that will
  be ordered by in your procedure
• Your Ordered by columns must be grouped based on
  data type!
• Your Order Direction must be accounted for as
  well (i.e. ascending or descending)

5
Sample Order By

• Demonstration Example Northwind Database
  Employee Sales By Country

6
(No Transcript)
7
COLD FUSION Implementation

• Simple as passing the parameters through the
  url or through a form
• i.e. ?bdate1/1/1900edate1/1/2005OrderbyCompan
  ySortDirectionasc

8
CFSTOREDPROC

• The call to the Procedure

9
Using the SQL 2K Web Assistant

• Using the Web Assistant to create some great
  simple reports leveraging your Database Server to
  Create the reports, in a great paginated way

10
Why?

• Let the Database create some great offline
  pages with pagination with NO Cold fusion coding
  needed
• Take some load off the Application Server
• Give your customers / clients their data by
  creating web assistants reports for stored procs
  youve already created

11
Keys to Success

• Need a flexible way to change your report without
  having to go through the Setup Wizard Each time
• Need a way to manually re-generate them, or
  schedule their execution (Job).

• Use a stored procedure that will execute the
  report (if report changes you can make the easy
  change w/in the proc.
• Use some specially crafted SQL to create usable
  HTML for your reports

12
Welcome to the Web Assistant Wizard
13
Adding Functionality

• Use SQL to create usable HTML elements
• Select ltinput typecheckbox nameorderid value
  convert(varchar(10),orders.orderid)
  onclickdocument.location.hrefordersummary.cfm?o
  rderid convert(varchar(10),orders.orderid) gt

14
Live Example

• Adding the SQL to the Stored Procedure that will
  give the reports some added usability and
  function
• Simple Checkbox with a link to a cfm page
• Ability to regenerate the Job via a link (EXEC
  sp_runwebtask _at_procname N'Northwind Web Page')