Stored Procedures in Database Design

Introduction

            Complex databases are large sources of a company’s information and should be able to be tracked and accessed when requested. However, without the right tools and steps in making a database or accessing data in the database it can result in large errors and potential damage the companies information. There are different ways to avoid catastrophic events like that but some solutions and practices will be much more effective depending on the desired end product or data.

Importance of Constraints, Views, Function, Stored Procedures in Database Design

            By having Layers of Abstractions such as Views, Functions, and stored procedures we can more carefully SELECT, INSERT, UPDATE AND DELETE data in a database. The layers of Abstraction help us immensely by not physically change the raw data in a database. There can be problems with directly working with the database which in large scale corporations have huge value. Database coding and the access of data should never directly be executed queries on your core tables in a data model. It creates security through restrictions on a user by not having them have direct access to a dataset. You can alter the data table to only see public parts and keeping private information secure. Not only that there can you can use joins to show information from two different tables.

Transaction Statements in a Stored Procedure

            Transactions are important in Stored Procedures by making modifying statements atomic meaning all steps either fail or pass. This makes queries only runnable without errors, if errors exist there it will fail and end the query. Errors can exist by trying to delete a foreign key with a constraint or trying to put an invalid type in a variable or parameter. Preventing errors in queries can protect against catastrophic problems in large scale systems such as power outages and hard drive crashes.

Summary

             When including Transaction Statements within a Stored Procedure as well as try/catch error handling, to have a plan for when the system fails, and return codes, to show any errors, there is more prevention to problems that would occur. Stored Procedures with the attributes above troubleshoot the procedure and can track logic that occurs making tracking errors easier and making them crucial to professional database design.

Works Cited

Mitchell, S. (2005, August 3). Managing Transactions in SQL Server Stored Procedures. Retrieved from http://www.4guysfromrolla.com/webtech/080305-1.shtml

Prato, A. (2007, August 22). Abstract Data with SQL Server Views, Stored Procedures and Functions. Retrieved from https://www.mssqltips.com/sqlservertip/1314/abstract-data-with-sql-server-views-stored-procedures-and-functions/