What Is a Stored Procedure in SQL Server and Why Use it?

An SQL Server Stored Procedure is a logical collection of statements or queries packaged together to perform a particular task, repeatedly. You do not have to write the statements repeatedly and it saves not just time but also other server resources.

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.

SQL Server Stored Procedure

Syntax

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.

* Speed

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.

* Security

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

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.

* Dependency

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.

* Expensive

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.

Conclusion

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.

Previous - Try… Catch – Exception Handling in SQL ServerNext - Find and Remove Duplicate Rows in a Table using SQL Server ROW_NUMBER() and CTE



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner

Related Posts: