Transcript Stored Procedures

Stored Procedures
(dynamic Order By + Web
Assistant Wizard)
Kevin Penny, MMCP
[email protected]
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)
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!)
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)
Sample Order By

Demonstration Example: Northwind
Database – Employee Sales By
Country
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=as
c
CFSTOREDPROC

The call to the Procedure:
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
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
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 regenerate 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
Welcome to the Web Assistant Wizard
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.cf
m?orderid=‘ + convert(varchar(10),orders.orderid)
+”>
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')