Notes on Stored Procedures

1. Notes on Stored Procedures CSSE333 Intro to Database Systems--Winter 2005-2006

2. Outline • Application Design • Tiered Architectures • O/R Mapping • The CRUD Pattern • Why use stored procedures? • Advice • Lab

3. Tiered Architectures • The standard three-tiered architecture consists of the presentation, application and data access tiers. • The problem is many applications require a high coupling between the bottom two tiers. Changing database architectures often dramatically affects the system at the application tier. • Can be due to poor design, but also strongly influenced by changing database connection technologies.

4. N-Tiers and so on … • There are many designs variations based on the 3-tier architecture. • Many introduce a data services layer to abstract the database connection technology. From: www.novicksoftware.com

5. Web Services for Data Access • Web services are Web-based enterprise applications that use open, XML-based standards and transport protocols to exchange data with calling clients. - Sun.com • SOAP ("Simple Object Access Protocol") is a simple XML-based protocol to let applications exchange information over HTTP. – W3C • Useful for distributed application designs. • Can be used to reduce coupling to database connection technologies. • Especially common in modern websites.

6. Persistence Framework • The application layer should use a persistence framework (or layer) to solve the coupling problem. • A good persistence framework will hide the fact that objects are even being stored in a relational database.

7. O/R Mapping • Most modern business application development projects use object technology such as Java or C# to build the application software and relational databases to store the data. • There is an impedance mismatch between object and relational design methods. • Object-relational mapping (also ORM) is used to map object-oriented programming objects to relational databases.

8. O/R Mapping - Additional Concepts • Shadow information is any data that objects need to maintain, above and beyond their normal domain data, to persist themselves. This typically includes primary key information, particularly when the primary key is a surrogate key that has no business meaning, concurrency control markings such as timestamps or incremental counters, and versioning numbers. … A common practice is for each class to implement an isPersistent boolean flag that is set to true when the data is read in from the database and set to false when the object is newly created. • Lazy Reads - An important performance consideration is whether the attribute should be automatically read in when the object is retrieved. When an attribute is very large, for example the picture of a person could be 100k whereas the rest of the attributes are less than 1k, and rarely accessed you may want to consider taking a lazy read approach. The basic idea is that instead of automatically bringing the attribute across the network when the object is read you instead retrieve it only when the attribute is actually needed. This can be accomplished by a getter method, an operation whose purpose is to provide the value of a single attribute, that checks to see if the attribute has been initialized and if not retrieves it from the database at that point. • From: http://www.ambysoft.com/essays/mappingObjects.html • Another decent reference: http://www.chimu.com/publications/objectRelational/

9. The CRUD Pattern • Organizes the persistence operations of an application into Create, Retrieve, Update and Delete operations that are implemented by a persistence layer. - Patterns in Java, Volume 3. • The CRUD pattern is usually implemented on the database. Stored procedures are the recommended method for SQL Server. • It is suggested that they be created in an automated manner. • They will have to deal with O/R mapping, but are not expected to contain application logic!!!

10. Why use stored procedures? The reasons for using Stored Procedures to implement the Data Storage Layer instead of allowing ad hoc SQL statements are: • The best possible performance • Removes the SQL code from the other layers of the application • Prevents SQL injection attacks • Prevents casual table browsing and modifications • Easy to create atomic transactions • Can catch exceptions • Recommended by vendors • Derived from: www.novicksoftware.com and Rob Howard's Blog

11. There is a debate Flame war really… • Issues with performance • Issues with DBA vs Developer control • Issues with DB portability • Issues with source control • Issues with dogma • Dynamically SQL & Parameterized Queries can be preferred • In general concatenated SQL is to be avoided to prevent injection attacks. string query = "SELECT * FROM Users WHERE UserID = '" + tbxUserId.Text + "'";

12. Theory & Practice • Business logic often gets into data layer • Many times designers model objects after data structure (and visa versa) • Many glue layers are tedious to create and maintain manually. • A many tiered application can seem over architected and may not have immediate advantages • Abstraction increases load • Legacy code complicates things • Best solution requires consideration of case

13. Additional Wisdom • Do not use a highly privileged SQL account (like SA) to access from the application. (Also do not use a highly privileged domain account). • Encrypt the DB connection string. • Avoid fetching static information from the DB every time you need it. Cache it in the application. • Do not use SELECT * … ALWAYS define the table columns once you have determined them. • Do not prefix user sprocs with sp_. This actually stands for ‘system procedure’. Your procedure may become inaccessible. • Use parameter validation at all layers. You may get unexpected results otherwise. Derived from Doug Seven’s “10 Things You Shouldn't Do with SQL Server”

14. Stored Procedures Are Code!!! • Use source control. (This can be difficult.) • Always include comments, good variable names, and exception handling. • Try to re-use code. T-SQL does not support OOP. However, you can call other sprocs and UDFs. • Test and validate the code.

15. New in SQL Server 2005 • CLR is embedded inside the database engine, meaning T-SQL functions can be converted to CLR functions for significant performance gains, and applications can be run inside the engine • A new tool called Service Broker also stands to alter the way applications are designed. The new feature is described as an asynchronous programming environment that creates a "persistent dialogue" between the client application and the server. • Offers native XML support - From SearchSQLServer.com

16. Today’s Lab • Probably really long, but hopefully fun • May not be good example of data layer abstraction • Good opportunity to understand possibilities for stored procedures • We will try to offer hints

17. Read Example - get_Order Details_1 -- -- Review products in order -- ------------------------------------------- -- Demo: -- DECLARE @Status SMALLINT -- EXEC @Status = [get_Order Details_1] 10501 -- SELECT Status = @Status ------------------------------------------- -- Revision History -- Created - WFM - 01/10/2005 CREATE PROCEDURE [get_Order Details_1] (@OrderID_1 [int]) AS -- Supress row count messages SET NOCOUNT ON -- Check to see if OrderID is valid IF (SELECT Count(OrderID) from Orders WHERE OrderID = @OrderID_1) <> 1 BEGIN PRINT "The OrderID " + CONVERT(varchar(30), @OrderID_1 ) + " does not exist" RETURN 1 END -- Retrieve the order details SELECT * FROM [Order Details] WHERE OrderID = @OrderID_1 -- Check for errors DECLARE @Status SMALLINT SET @Status = @@ERROR IF @Status <> 0 BEGIN -- Return error code to the calling program to indicate failure. PRINT "An error occurred getting the details for the order " + CONVERT(varchar(30), @OrderID_1) + "." RETURN(@Status) END ELSE BEGIN -- Return 0 to the calling program to indicate success. PRINT "The details for the order " + CONVERT(varchar(30), @OrderID_1) + " were retrieved successfully." RETURN(0) END GO

18. Update Example - update_Order Details_1 -- -- Adjust qnty, price, or discount for products on order -- -- NOTE: This is script is for hints and is not a complete solution -- ------------------------------------------- -- Demo: -- DECLARE @Status SMALLINT -- EXEC @Status = [update_Order Details_1] 10501, 2 -- SELECT Status = @Status -- -- SELECT * FROM [Order Details] WHERE OrderID = 10501 -- SELECT * FROM Products WHERE ProductID = 2 ------------------------------------------- -- Revision History -- Created - WFM - 01/10/2005 CREATE PROCEDURE [update_Order Details_1] (@OrderID_1 [int], @ProductID_2 [int], @NewQuantity_4 [smallint]=0, @NewUnitPrice_3 [money] = 0, @NewDiscount_5 [real] = 0) AS -- Supress row count messages SET NOCOUNT ON -- Check to see if product needs to be reordered DECLARE @UnitsInStock INT DECLARE @ReorderLevel INT SELECT @UnitsInStock=UnitsInStock , @ReorderLevel = ReorderLevel FROM products WHERE ProductID = @ProductID_2 IF (@UnitsInStock < @ReorderLevel) BEGIN UPDATE PRODUCTS SET UnitsOnOrder = @ReorderLevel*5 WHERE ProductID = @ProductID_2 END -- Update Order Detail values UPDATE [Northwind].[dbo].[Order Details] SET [UnitPrice] = @NewUnitPrice_3, [Quantity] = @NewQuantity_4, [Discount] = @NewDiscount_5 WHERE ( [OrderID] = @OrderID_1 AND [ProductID] = @ProductID_2) GO