Stored Procedures: One of the most beneficial reasons to use stored procedures
is the added layer of security that can be placed on the database from the calling
applications. If the user account created for the application or web site is configured
with EXECUTE permissions only then the underlying tables cannot be accessed directly
by the user account. This helps prevent hacking directly into the database tables.
The risk of a hacker using the user account to run a stored procedure that has been
written is far safer than having the user account have full insert, update and delete
authority on the tables directly.
Another advantage to using stored procedures, especially in medium to large scale web sites or applications, is the data functionality is separated from the application making it easier to manage, document, and maintain. For example, if an application updates the customer table in ten different places, there can be a single stored procedure and a standard procedure call from the application for this functionality. If a change needs to be made to the way a customer record is managed, then the SQL statements only need to be changed in one place, in the database layer. In most cases, the application is not affected unless the procedure call requires modification. Changing the procedure call is also easier, because a standard call is already in place. Managing the data in the data layer avoids having to keep track of embedded SQL calls that may be different in each place, whenever a change is required.
Stored procedures provide improved performance because fewer calls need to be sent to the database. For example, if a stored procedure has four SQL statements in the code, then there only needs to be a single call to the database instead of four calls for each individual SQL statement. Of course there is always a tradeoff. There is an increased workload on the server side that needs to be taken into account.
Another advantage to using stored procedures allows for multiple client applications written in any language and running on any platform to have consistent database routines. Each application uses the same procedures and simply has to embed a standard procedure call for the language in the calling program.
SQL Script on the fly: In some applications having hard coded SQL statements is not appealing, because of the dynamic nature of the queries being issued against the database server. Because of this sometimes there is a need to dynamically create a SQL statement on the fly and then run that command. This can be done quite simply from the application perspective where the statement is built on the fly whether you are using ASP.NET.
When we need to solve a tricky database problem, the ability to generate SQL statements is a powerful tool. A dynamic SQL statement is constructed at execution time, for which different conditions generate different SQL statements.
Another advantage to using stored procedures, especially in medium to large scale web sites or applications, is the data functionality is separated from the application making it easier to manage, document, and maintain. For example, if an application updates the customer table in ten different places, there can be a single stored procedure and a standard procedure call from the application for this functionality. If a change needs to be made to the way a customer record is managed, then the SQL statements only need to be changed in one place, in the database layer. In most cases, the application is not affected unless the procedure call requires modification. Changing the procedure call is also easier, because a standard call is already in place. Managing the data in the data layer avoids having to keep track of embedded SQL calls that may be different in each place, whenever a change is required.
Stored procedures provide improved performance because fewer calls need to be sent to the database. For example, if a stored procedure has four SQL statements in the code, then there only needs to be a single call to the database instead of four calls for each individual SQL statement. Of course there is always a tradeoff. There is an increased workload on the server side that needs to be taken into account.
Another advantage to using stored procedures allows for multiple client applications written in any language and running on any platform to have consistent database routines. Each application uses the same procedures and simply has to embed a standard procedure call for the language in the calling program.
SQL Script on the fly: In some applications having hard coded SQL statements is not appealing, because of the dynamic nature of the queries being issued against the database server. Because of this sometimes there is a need to dynamically create a SQL statement on the fly and then run that command. This can be done quite simply from the application perspective where the statement is built on the fly whether you are using ASP.NET.
When we need to solve a tricky database problem, the ability to generate SQL statements is a powerful tool. A dynamic SQL statement is constructed at execution time, for which different conditions generate different SQL statements.
No comments:
Post a Comment