SPs are an advanced feature in SQL server that offers you to create, compile and run SQL statements in the server itself, to isolate your business logic from data logic and to improve the performance of your application.
In short, write SQL queries in a specific format in the SQL server and call them from your application, instead of writing queries inside your program code.
1.Mainly to increase the performance and the momentum to our programs. When you write a stored procedure, it will be pre-compiled by the SQL server, so that it can increase the speed of executing the queries and hence your application. When you write a stored procedure the database(DB) server automatically generates an execution plan for the procedure. The plan will be updated whenever a change is made in the procedure by the DB server.
2.You can take away all your SQL commands so that the data logic can be isolated from your business logic(ie.,Coding). This kind of encapsulation helps the web server to read and interpret lengthy and complex SQL commands.
3.Then like COM, stored procedures can be reused. For example if you want to do the same query in two different ASP pages or in VB forms, you can reuse the stored procedure which you have written for one page, It saves you time.
4.The application code as well as the stored procedure code are becoming easy to maintain. Updating a stored procedure may not affect the other part of an application or user who uses the same stored procedure.
5.The queries can be customized by using input/output parameters, like functions and procedures which you write in your programming languages.
SPs are best because It is Tytly cuppled while Query on code page it is Loosly cuppled...
ReplyDelete