STORED Procedures:good or bad (stored procedure: Yes or no)

Stored Procedures
Author

Date of submission

User level

Saikalyan Prasad Rao

07/06/2004

Intermediate

Author

Date of submission

User Level

Saikalyan Prasad Rao

07/06/2004

Intermediate



I am sure this issue has been taken up and discussed in lots of of articles on the net. This article aims in both sides of the coin. We'll dwell on the advantages of Stored procedures.



I'm sure this problem has been discussed many times on the Internet. This article is discussed from two aspects. First, let's discuss the advantages of the stored procedure in detail.

Stored procedures provide performance benefits such as local to database, pre-compiling and caching, a programming Framewo RK with the use of input/output parameters, reuse of procedures and security feature such as encryption and privilege limits T o users. A part from that it offers modularization of code and changes are immediately affected unlike business components which NE Ed to be recompiled and deployed. Not forgetting this with the advent of. Net, deployment issues have been a reduced quite. But nevertheless changes made to any component does need to be rebuilt. The other benefits include saving in round trips to the client apps and reduction of network traffic.



Stored procedures provide features such as data localization, preprocessing, and buffering, a schema that uses input and output parameters that can be reused to restrict users by reusing stored procedures and security features as encryption and permission settings. Part of it comes from its code modularity, which does not need to be recompiled and deployed as a business component. Don't forget to do it yourself. NET, deployment has been greatly simplified, but changes to any component still need to be recompiled. Another benefit is the saving of interaction time with the client application and the acceleration of the network response.

But on the flip side Stored procedures does come with its own share of problems. Debugging and maintenance has always been a known issue and it makes it even the more difficult when developers like me ge T used to vs.net debugger. On a side note, I do the Microsoft has always built a very good debugger in vs/vs.net.



However, another side effect of the stored procedure arises from itself. Debugging and maintenance have become a well-known topic, which can be more difficult for developers like me who are accustomed to the Vs.net debugger. From a certain point of view, I think Microsoft Vs/vs. NET debugger does not always perform well.

Managing changes in stored procedures and applying Service pack releases can is a bit teething at times. Apart from-there are issues pertaining to migration. What if your application which is built with SQL Server needs to is ported to Oracle or no other database? It would/is A nightmare converting all those stored procedures and T-SQL specific code to a Compatible/ansi SQL code for T Hat database.



Sometimes changes in stored procedures and application Service pack applications may be somewhat inconvenient. In addition to this, there is a transplant problem. If your application is written in SQL Sever, what do you do now to migrate to Oracle or other databases? It is a nightmare to turn all stored procedures and T-SQL code into a SQL code that is compatible with the target database.

Personally, I would like into go in for stored procedures and leverage most of the database capabilities if I knew my projec T is going to use a specific database and wouldn ' t. I am sure many must is thinking on the same lines. After all one of the ' cool features ' I liked about SQL Server was it support for XML. You are should try doing bulk updates through XML, works like a charm and that too with less of code. In fact into. Net, datasets have the capability to output out XML representation of data which saves your effort of Writi Ng code to formulate the XML. Pumping in the Business Logic in Stored procedures have the done and been a makes of lot for sense. But If you are want to scale up your application it poses a problem since your database and business logic get tied to your DA Tabase tier. I am sure for small projects it wouldn ' t matter more but for a large scale enterprise level solution this would at some PO int in time pose a huge problem.



I advocate using stored procedures if I know that my project works with a specific database and does not change. I believe many people have the same idea. After all, SQL Server has a feature that can support XML. You can use XML to make a lot of data updates. In fact, in. NET, a dataset can export stored data to XML files in XML data representations. The introduction of business logic in stored procedures makes many small projects very easy. However, if you want to expand your application, it will create a problem because your database and business logic are bundled in your database tier. This is not important for small projects, but it can be a big problem for large enterprise-level solutions.

I am sure there'll always be two different schools of thoughts on whether or do not have stored. All said and do, it does raise an interesting issue. If we weren ' t to use stored procedures, what could is an alternative? Different solutions come to mind such as a generic DB layer component which would have all ANSI SQL statements which would Allow one to connect to various databases or the Ad-hoc SQL approach. But Both of approaches do come with its share of hurdles and pitfalls. We know how brittle ad-hoc scripts are since no small change to the database could have sever impacts on your system. Building a generic DB component needs to have a properly designed database which would get affected every time your datab ASE changes.



I'm sure there are different opinions about whether to use stored procedures. This can cause a very interesting topic. What are the workarounds if we don't use stored procedures? Different solutions lead to the creation of such a database-tier component: It has all ANSI SQL expressions to accommodate a variety of different databases or specific SQL methods. But both of these methods have common drawbacks. As we all know, specific scripts are very fragile, and any small changes in the database can affect your system. Building a common database component requires a well-designed database to accept changes to the database.

I guess with both sides has its own share of advantages and disadvantages, I feel the best approach would being is Best of both the worlds. All insertions, updating, selects etc to is done in stored procedures which would enable me to leverage some of the "cool F" Eatures of SQL Server like XML updates and put the business logic into components which would me to allow debug and Scale them.



I guess both of these have their pros and cons, and I think the best solution is to look at the problem in two ways. All inserts, updates, select, and so on can be done in a stored procedure that allows me to use SQL Server's XML update functionality, while putting business logic into components that allow me to easily debug and extend.

The upcoming release of SQL Server "Yukon" and asp.net "Whidbey" aims for address these issues. Yukon is coming the up and inbuilt support for CLR. That's means we can now code stored procedures into any of the. Net languages which are easier to write than T-SQL and at the Ame time leverage the powerful debugging features of Vs.net. In asp.net "Whidbey" There are plans of the introducing a new extensibility point called Providers. This is the new Provider Model would support many new features likes, membership, role Manager, Site personalization, Build Providers, and Health monitoring etc. The Provider Model in asp.net Whidbey enables developers to completely un-plug the logic/behavior/data interaction of a PA Rticular feature of ASP.net and replaces it with one ' own logic/data. In the Provider model provides both data and business logic abstraction.



The upcoming release of SQL Server "Yukon" and asp.net "Whidbey" are all addressed to this issue. Yukon supports the CLR. This means that we can write stored procedures in a vs.net with powerful debugging capabilities, using any. NET language instead of T-SQL. In asp.net "Whidbey", there are plans to propose an extensible approach called provider. This new provider model supports a number of new features, such as membership, personalization, role Manager, Site navigation, build providers, and health Monitoring and so on. The provider model in ASP.net Whidbey allows the developer to completely remove the logic/behavior/data interactions in asp.net and replace it with a logical/data layer. In short, Provider Model abstracts data and business logic.

Both the upcoming releases have tried to bridge the gaps. I for one am eagerly waiting for their respective releases to happen. Whether successful or not.



I've been waiting for the release of their respective versions. Whether you succeed or not, time will prove everything.