Here, in this article I’ll explain in detail about the advantages and disadvantages of using Stored Procedure in SQL Server. In-fact it’s common among all RDBMS’s that follows SQL standards.
CREATE PROCEDURE [dbo].[Procedure_Name] @Input_Parameter1 NUMERIC, @Output_Parameter2 VARCHAR(10) OUTPUT -- OPTIONAL AS BEGIN SQL Statements... RETURN END
Advantages of Stored Procedures
Stored Procedures are one of most popular, reliable and widely used database object and as expected, it too comes with its share of Advantages and Disadvantages.
* Developer Friendly (Reusability)
Talking about saving time, developers do not have worry about writing queries multiple times in applications. Store Procedures allow developers to write SQL statements once and use them repeatedly and share the data multiple times on a distributed environment.
Departments such as Accounts and HR share similar data. Therefore, queries written in a single Stored Procedure can provide data to both the departments. You do not have write similar queries for separate departments.
Application developers can sometimes keep the business logic separate from the main application and any changes done in the procedure will avoid recompilation of the entire application.
* Improve Performance
Stored Procedures are a boon to servers also, since they reduce unnecessary burden on the servers we work on. An online application can send thousands of data at a time and can put extra stress on the Bandwidth and later on the Server.
A Stored procedure can reduce the stress and load to the bandwidth and server. The procedure accepts limited and meaningful amount of data through its predefined parameters, which then processed by the procedure itself.
Stored procedures are compiled only once during its creation, and it is cached in the server. Therefore, repeated compilation is not required during execution of the procedure, thereby increasing the speed of execution. However, if any updates are made in the procedure then it will be re-compiled.
One very useful accepts of using a stored procedures is the security cover it provides to its data source.
Since there are no direct accesses to the tables, it prevents unauthorized manipulation of records inside the tables. A properly written procedure acts as a solid deterrence to SQL injections.
SQL injections are a combination of SQL statements and techniques that can break into databases and do unauthorized manipulation to the data.
* Set Based Processing
Set based processing usually refers to processing bulk data at once and it is much quicker. This is another advantage of using a stored procedure since it processes a bulk of data. Distributed applications across the network, rely heavily on these procedures as it reduces round trips.
Disadvantages of Stored Procedures
Debugging is never easy, so it is not advisable to write and execute complex business logic using stored procedures. Therefore, if not handle properly it can lead to a mess and can give nightmares to DBAs.
In a large enterprise, seasoned DBAs and database developers manage huge data pools. Application developers, on the other hand have to rely on them, as for any minor change they have refer it to a DBA, who will fix the bugs in existing procedures or create new procedures.
Don't get wrong on this. DBAs never come easy, never come cheap. Companies will have to incur an extra cost on hiring experts DBAs, who we believe are much more qualified in handling complex database procedures.
* Vendor Specific
Stored procedures often written in one platform may not run on another platform. Procedures written in Oracle are more sophisticated and you might have to write the entire procedure again for SQL Server.
Create a Stored Procedure
We will create a simple procedure that does not take any parameter. Upon executing, the procedure will show a list of books.
CREATE PROCEDURE dbo.GetBookDetails AS BEGIN SELECT *FROM dbo.Books END EXEC dbo.GetBookDetails
It is a very basic procedure and does not mean much.
There are two separate ways to call a stored procedure. You can type the procedure name and press the button F5 (SQL Server).
dbo.GetBookDetails – and press F5
The second and more efficient way is to type EXECUTE or EXEC followed by the procedure name.
EXECUTE dbo.GetBookDetails OR EXEC dbo.GetBookDetails
Parameters in a Stored Procedure
The @ sign are used as a prefix in all the parameters in a stored procedure. The procedure can have a combination of INPUT or OUPUT parameters. You must define the Input parameters in the beginning followed by the Output parameter, if any.
Output parameters are optional, that is, the procedure may or may not return any value.
CREATE PROCEDURE dbo.Division @val1 INT, @val2 INT AS BEGIN BEGIN TRY SELECT @val1 / @val2 AS Final_Value END TRY BEGIN CATCH SELECT 'AN ERROR OCCURED: ' + ERROR_MESSAGE() AS Custom_Error END CATCH END EXECUTE dbo.Division 10, 2
The above stored procedure accepts two parameters of datatype INT. While executing the procedure, we will pass two values separated by a comma to get a desired result.
Also in the procedure, we have declared the exception handling procedures using TRY…CATCH.
Drop A Stored Procedure
At any stage if you wish to drop or remove the stored procedure from the database, then use the Drop ItDROP PROCEDURE
Before dropping, simply make sure it is free of any kind of relation with any other object. It might get you in trouble. Good or bad, it has existed for a very long time and used by developers worldwide with a common motive, for enhancing performance, bulk execution and security. Write once, use it repeatedly in a distributed environment is always good. However, do keep a track of all the procedures if they are related, then you might have to make changes accordingly in multiple procedures. Thanks for reading.
DROP PROCEDURE dbo.Procedure_Name
Before dropping, simply make sure it is free of any kind of relation with any other object. It might get you in trouble.
Good or bad, it has existed for a very long time and used by developers worldwide with a common motive, for enhancing performance, bulk execution and security. Write once, use it repeatedly in a distributed environment is always good. However, do keep a track of all the procedures if they are related, then you might have to make changes accordingly in multiple procedures.
Thanks for reading.