Why Use Stored Procedures?


By using stored procedures, we can reduce the time a process can take, as stored procedures are compiled.

Another gain from using stored procedures is that they are much simpler to maintain, as compared to raw T-SQL code. Since the stored procedure is held centrally within the database, any required bug fix, upgrade, or modification can be completed centrally, thus reducing the downtime. If the code is embedded within a program, as in VB.NET, we have to not only change the code in one central place but also distribute it to every client running the software. Modification of web sites also brings in its own set of problems, and rolling out a new ASP.NET client can prove problematic. This can be settled by using stored procedures.

The code in a stored procedure is executed in a single batch of work. This means that it is not necessary to include a GO command while writing code for procedures. SQL Server will take any of those statements and implicitly execute them, as a batch.

Perhaps, the greatest reason to create a stored procedure rather than use inline T-SQL, is security. As we will discuss in Chapter 8, we have several security options at our fingertips while using a stored procedure. Along with other security measures, it helps to ensure that we can lock down our database from prying eyes.

Note

Stored procedures are rarely used for creating other database objects with the exception of temporary tables, which are special tables created to complete a task within a stored procedure. There is more on temporary tables in Chapter 4.




SQL Server 2000 Stored Procedures Handbook
SQL Server 2000 Stored Procedures Handbook (Experts Voice)
ISBN: 1590592875
EAN: 2147483647
Year: 2005
Pages: 100

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net