Result Set Production With Stored Procedures

Labels
AJAX(112) App Studio(6) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(8) OAuth Scopes(1) OAuth2(11) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(10) PKCE(2) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(178) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(80) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Sunday, April 29, 2012PrintSubscribe
Result Set Production With Stored Procedures

Modern database servers provide support for stored procedures. A stored procedure is a script written in a server-specific dialect of SQL. Such scripts are stored in the database and executed by a database server process upon request.

The main benefits are the close proximity to the data and reusability of the procedure. Applications invoke stored procedures by name with parameters that affect the execution result.

A popular reason to implement a stored procedure is the need to produce a custom result set that cannot be achieved with a SELECT statement. Sometimes the database table data needs to be split into multiple streams, merged, pivoted, and conditionally processed over multiple iterations.  A custom result set is returned to the client application in the stored procedure output.

Frequently, it takes a longer time to produce a custom result set with a stored procedure when compared to a straightforward SELECT statement. This creates a unique challenge in presenting the output of stored procedures in the user interface of applications. End users may be willing to wait for initial results, but will quickly grow irritated if every single interaction with the application takes a long time to complete.

Output caching must be employed when working with the stored procedures designed to produce custom result sets.