1 / 14

Stored Procedures (dynamic Order By + Web Assistant Wizard)

Stored Procedures (dynamic Order By + Web Assistant Wizard). Kevin Penny, MMCP kevin@hotgigs.com. Advantages. Pre-compilation = faster execution Less Data transferred across network EXAMPLE: exec sp_getproducts 12,234 vs.

clinton
Download Presentation

Stored Procedures (dynamic Order By + Web Assistant Wizard)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Stored Procedures(dynamic Order By + Web Assistant Wizard) Kevin Penny, MMCP kevin@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. COLD FUSION Implementation • Simple as passing the ‘parameters’ through the url or through a form • i.e. ?bdate=1/1/1900&edate=1/1/2005&Orderby=Company&SortDirection=asc

  7. CFSTOREDPROC • The call to the Procedure:

  8. Using the Web Assistant to create some great simple reports leveraging your Database Server to Create the reports, in a great paginated way Using the SQL 2K Web Assistant

  9. 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 you’ve already created

  10. 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 Keys to Success

  11. Welcome to the Web Assistant Wizard

  12. Adding Functionality • Use SQL to create usable HTML elements • Select ‘<input type=checkbox name=orderid value=‘ + convert(varchar(10),orders.orderid) + ‘ onclick=document.location.href=“ordersummary.cfm?orderid=‘ + convert(varchar(10),orders.orderid) +”>

  13. 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 @procname = N'Northwind Web Page')

More Related