Stored Procedure in SQL Server

Stored Procedure Overview

• A Stored procedure is a group of one or more pre-complied SQL statements or SQL code that you create so you can reuse the code over and over again. 
• It is stored as an object inside the database server. Each procedure contains a name, parameter lists, and T-SQL statements.
• We can invoke procedures by using triggers, other procedures, and other applications like Java, .Net, Python, etc.
• It supports almost all the relational database systems (SQL Server, Oracle, MYSQL, etc.).
• SQL server builds an execution plan when the stored procedure is called the first time and stored in cache memory.

How does the Stored procedure work?

There are two kinds of parameters in the stored procedure as follows.

• Input parameters - user passing value to stored procedures.
• Output parameters - return the value from stored procedures.

It accepts input parameters and returns multiple values in the form of output parameters to the calling procedure or batch.

Advantages of stored procedures

• The stored procedure reduces network traffic between the application and the database server.
• Reusability - it reduces code inconsistency and prevents unnecessary writes of the code
• Security- stored proc is always secure because it grants limited access to users on a need basis and manages what processes and activities we can perform.
• Avoids SQL injection attacks.

Disadvantages of stored procedures

• Debugging is a little difficult 
• Testing stored procedure - any data errors in handling errors are not generated until runtime.

Types of Stored Procedure

• User-defined Stored Procedures
• System Stored Procedures

Let’s create an Employee table and understand how to create user-defined stored procedures by taking some examples:

CREATE TABLE Employee (
EMPID INT PRIMARY KEY,
EMPName VARCHAR (50),
EMPAddress VARCHAR (Max),
EMPAge INT,
EMPSalary INT
)

--Inserting multiple records in one table.

INSERT INTO Employee (EMPID, EMPName, EMPAddress, EMPAge, EMPSalary)  
VALUES (101, 'Harsha', 'Hyderabad', 28, 65000),
(102, 'Rohit', 'Pune', 32, 55000),
(103, 'Karan', 'Mumbai', 42, 95000),
(104, 'Faizal', 'Hyderbad', 30, 35000)

Now check the table by running a select query.

SELECT * FROM Employee;

The employee table looks like the one below.

Now let’s create a Simple Stored Procedure.

Example 1. (Simple Stored Procedure for Select operation without any parameter).

CREATE OR ALTER PROC usp_EmployeeRecords
AS
BEGIN
	SELECT * FROM Employee 
END

Stored procedure usp_EmployeeRecords was created, as you can see in the above screenshot. Now let’s execute the same procedure.

EXECUTE usp_EmployeeRecords;

After executing the procedure, we can see all the employee records are there in the results.

Example 2. (Stored Procedure with input parameter to insert record).

Now let’s create a stored procedure that accepts input parameters insert new records into the employee table, and shows all the records of the employee table.

CREATE OR ALTER PROC usp_InsertEmployeeRecords
@EMPID INT,
@EMPName VARCHAR (50),
@EMPAddress VARCHAR (Max),
@EMPAge INT,
@EMPSalary INT
AS
BEGIN
	INSERT INTO Employee VALUES (@EMPID, @EMPName, @EMPAddress, @EMPAge, @EMPSalary)
	SELECT * FROM Employee	--Checking if all the records inserted in Employee table or not.
END

As you can see in the above screenshot, the stored procedure usp_InsertEmployeeRecords is created. Now let’s execute the same procedure by passing parameter values.

EXECUTE usp_InsertEmployeeRecords 105, 'Adil', 'Mumbai', 38, 70000

After executing the procedure, we can see a new record inserted in the employee table.

Example 3. (Stored Procedure with input parameter to update record).

Now let’s create a stored procedure that updates the records in the table.

CREATE OR ALTER PROC usp_UpdateEmployeeRecords
@EMPID INT,
@EMPAddress VARCHAR (Max),
@EMPSalary INT
AS
BEGIN
	UPDATE Employee 
	SET EMPAddress = @EMPAddress, EMPSalary = @EMPSalary 
	WHERE EMPID = @EMPID
	SELECT * FROM Employee WHERE EMPID = @EMPID    --Giving where condition along with select clause to retrieve only updated records 
END

As you can see in the above screen shot, the Stored procedure usp_UpdateEmployeeRecords was created. Now let’s execute the same procedure by passing parameter values.

EXECUTE usp_UpdateEmployeeRecords 105, 'Delhi', 75000

After executing the procedure, we can see the record is updated for EMPID “105” in the employee table.

Example 4. (Stored Procedure with input parameter to delete a record).

Now let’s create a stored procedure that deletes records from the table by passing only one parameter.

CREATE OR ALTER PROC usp_DeleteEmployeeRecords
@EMPID INT
AS
BEGIN
	DELETE FROM Employee WHERE EMPID = @EMPID  
	SELECT * FROM Employee           
END

Stored procedure usp_DeleteEmployeeRecords was created, as you can see in the above screenshot. Now let’s execute the same procedure by passing parameter values.

EXECUTE usp_DeleteEmployeeRecords 105  -- we pass the EMPID here to delete particular record

After executing the procedure, we can see the record is deleted for EMPID “105” in the employee table.

Example 5. (Stored Procedure with input and output parameters).

In programming, we create a function to return the value the same way we create a stored procedure to return the value. Now let’s create a Stored procedure that returns the salary of the employee whose EMPName will be passed to that stored proc.

CREATE OR ALTER PROC usp_SearchEmployeeSalary
@EMPName VARCHAR (50),
@EMPSalary INT OUTPUT
AS
BEGIN
	SELECT @EMPSalary = EMPSalary FROM Employee WHERE EMPName = @EMPName
END

Stored procedure usp_SearchEmployeeSalary was created, as you can see in the above screenshot. Now let’s execute the same procedure that returns the salary of the employee whose EMPName will be passed to that stored proc.

DECLARE @Result INT
EXECUTE usp_SearchEmployeeSalary 'Karan', @Result OUTPUT
SELECT @Result AS EmployeeSalary

After executing the procedure, it retrieves the employee's salary whose EMPName passed to the procedure.

Example 6. (Stored Procedure with input and output parameters).

Let’s create a procedure to count how many employees are in the table whose salary is greater than or equal to 50,000.

CREATE OR ALTER PROC usp_TotalEmployeeCount
@TotalCount INT OUTPUT
AS
BEGIN
	SELECT @TotalCount = count(EMPID) FROM Employee WHERE EMPSalary >= 50000	
END

Stored procedure usp_TotalEmployeeCount was created, as you can see in the above screenshot. Now let’s execute the same procedure that counts how many employees are there in the table whose salary is greater than or equal to 50000.

DECLARE @Result INT
EXECUTE usp_TotalEmployeeCount @Result OUTPUT
Select @Result AS TotalEmpCount

After executing the procedure, it gives the count of how many employees are there in the table whose salary is greater than or equal to 50,000.

Conclusion

In this article, we have learned about the stored procedure, its advantages, disadvantages, types, etc. We have also gone through the examples of how to create stored procedures with and without parameters. Hope you liked it. Please share your feedback and suggestions in the comments section.