RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Oracle Has SQL Server Beat in Stored Procedure Programming

Oracle9i and SQL Server 2000 add enhancements to the standard SQL language that enable you to use the database to implement your business rules and processes—SQL Server with Transact-SQL and Oracle with PL/SQL. Find out why the Oracle database engine is better suited for this role.

oth Oracle9i and SQL Server 2000 support the use of the database as more than a simple data store for your information. They enable you to use the database to implement your business rules and processes as well with enhancements to the standard SQL language that support the more complicated SQL necessary for this feature. However, each product does so in a different way. This article compares the two approaches and determines which database engine is more suited for this role.

SQL Server and Transact-SQL
SQL Server's enhanced version of SQL is called Transact-SQL (T-SQL). T-SQL is very much like SQL with a couple of crucial additions: it supports stored procedures and user-defined functions. These two features, as in any programming language, allow you to package your code into modular sections.

T-SQL also supports simple looping and branching constructs such as the 'goto, while' loop and 'if else' statements. In addition, SQL Server provides a library of useful functions in areas such as date and time, math, and data conversion. T-SQL also allows you to declare variables of the various SQL types and retrieve data from the database directly into these variables. You can create temporary tables (or a table variable) for storing intermediate results too.

Because T-SQL is very similar to SQL, programmers who are comfortable with SQL will find it quite easy to learn. For those who need more assistance, SQL Server comes with a very well indexed and searchable help system called Books Online that can quickly provide examples of the necessary syntax or more detailed explanations.

So how well does T-SQL do when programming systems with stored procedures? In my experience, you can support very complex and large systems this way. I currently work on several systems that consist of close to 1,000 stored procedures. To do so successfully, you need to carefully plan how to structure and modularize your code in a standard way. For example, my organization employs a standard way of writing updates, selects, and any other SQL operation to the systems I support. We also have standards for naming variables and stored procedures.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date