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

Here, in this article I’ll explain in detail about the advantages and disadvantages of using Stored Procedure in SQL Server. In-addition, I'll share examples of Strored Procedure. In-fact it’s common among all RDBMS’s that follows SQL standards.

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.

First, let us understand its advantages.

* 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.

Stored Procedure Example (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
	
-- Execute the procedure
EXEC dbo.GetBookDetails

It is a very basic procedure.

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

DROP PROCEDURE dbo.Procedure_Name

In the first example above, I have added a comment while executing the procedure. Here's how a comment in SQL Server looks like.

-- Execute the procedure
EXEC dbo.GetBookDetails

See the sentence in green color. Its a single like comment, where a sentence is preceded by two hypens (--).

If the comment has multiple lines, you can use a Block Comment (/* ... */) like this.

/* Execute the procedure.
The procedure takes 0 parameters */

Comments in SQL are useful. You must use it (however, do not overuse it). It makes the code more readable and easy to understand.

When you share a bunch of procedures or functions with other deverlopers or DBAs, the comments will make it easy for them to understand the code, its execution and the expected result, which will help them to upgrade the code (if at all necessary), with no or mimimum bugs.

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.

-------------------

Here's a list of Top 5 popular SQL Server posts on encodedna.com.

1) How to find and remove Duplicate rows in a Table using SQL Server ROW_NUMBER() and CTE: Duplicate rows in tables can be very annoying for DBA’s and programmers, as it raises many uncomfortable questions about the authenticity of data in a database. The matter gets worse when company auditors complain about irregularities in the balance sheet etc.

2) How to convert Rows into Columns using SQL Server PIVOT OR how to use PIVOT in SQL Server: Ever wondered how you can convert data from rows to columns in SQL Server. We are talking about an SQL query, which will transform records from multiple rows into columns. Using SQL Server PIVOT, we can efficiently rotate a table’s data to show a summarized result.

3) Insert Multiple rows with a Single INSERT Statement using SQL Server Table Value Constructor: While managing an Inventory management System for an organization, I have came across a situation where I had to perform bulk upload on a table in SQL Server. Bulk upload requires inserting multiple rows of data in a table.

4) How to Convert Month Number in a Date to Month Name in SQL Server: Let us assume I have a Sales table with sales data for each month. Every day sales is stored in the table with columns such as date, quantity, price etc. I want to get the total sales for every month. Now since I have a column with “date” data type, I want to convert the month number in the date to Month Name (like February, March etc.). Find out how this is done in SQL Server.

5) SQL Server CHARINDEX Function with Examples: The primary use of an SQL Server CHARINDEX function is to find the first or starting location of an expression or characters in a given string. To make it simple, it is like searching a specified character or characters in a string.

← PreviousNext →