+91-90427 10472

Dot net training in Chennai -Maria Academy

Stored Procedure in SQL Server

05 Apr 2023

A stored procedure is a precompiled and stored database object that contains a set of SQL statements and procedural logic that can be executed as a single unit. It can be used in a database management system to simplify complex queries and database operations. It is a prepared SQL code that you can save, so the code can be reused over and over again. Stored procedures help improve performance and security, as well as simplify the code.

Here’s a basic example of a stored procedure that accepts two input parameters and returns the sum of those two numbers:

CREATE PROCEDURE AddNumbers
@n1 INT,
@n2 INT
AS
BEGIN
SET NOCOUNT ON;
SELECT @n1 + @n2 AS ‘Sum’;
END

In this example, CREATE PROCEDURE is used to define the name of the procedure (AddNumbers) and its input parameters (@n1 and @n2). The AS keyword is used to begin the code block for the stored procedure.

SET NOCOUNT ON is used to prevent the count of the number of rows affected by the stored procedure from being returned.

The code block then calculates the sum of the two input parameters and returns the result using the SELECT statement.

Once the stored procedure is defined, it can be executed by calling its name:

EXEC AddNumbers 2, 3;

This will return the result 5 as the sum of 2 and 3.

Stored Procedure in a Basic CRUD Operation

Now, let us have an idea about using stored procedure in a basic CRUD operation sample.

Let us create a database as follows:

Create database Company

Then create a table in the corresponding database.

CREATE TABLE Employee(
EmpCode int,
EmpName nchar(100),
EmpAge int,
EmpSal int)

Inserting data into a table: This stored procedure inserts a new employee record into a table named Employee:

CREATE PROCEDURE AddEmployee
@EmpCode int,
@EmpName nchar(100),
@EmpAge int,
@EmpSal int
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Employee(EmpCode, EmpName, EmpAge, EmpSal)
VALUES (@EmpCode, @EmpName, @EmpAge, @EmpSal);
END

On executing the above snippet, a stored procedure to add the details in the table will be created.

Now, by calling the below query, the table will get added the following 5 datas.

EXEC AddEmployee 1, ‘TOM’, 25, 30000
EXEC AddEmployee 2, ‘RAM’, 25, 30000
EXEC AddEmployee 3, ‘RAJA’, 26, 32000
EXEC AddEmployee 4, ‘SIVA’, 26, 32000
EXEC AddEmployee 5, ‘GOPI’, 25, 30000

Retrieving data from a table: This stored procedure retrieves all the rows from a table named Employee:

CREATE PROCEDURE GetEmployees
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Employee;
END

On executing the above snippet, a stored procedure to get the employee details from the table will be created.

Then by simply calling the below query, the datas stored in the table can be retrieved.

EXEC GetEmployees

Updating data in a table: This stored procedure updates an existing employee record in a table named Employee:

CREATE PROCEDURE UpdateEmployee
@EmpCode int,
@EmpName nchar(100),
@EmpAge int,
@EmpSal int
AS
BEGIN
SET NOCOUNT ON;
UPDATE Employee SET
EmpName = @EmpName,
EmpAge = @EmpAge,
EmpSal = @EmpSal
WHERE EmpCode = @EmpCode;
END

On executing the above snippet, a stored procedure to update the details in the table will be created.

Now, by calling the below query, the data with “EmpCode =1” in the table can be updated with new data as follows.

EXEC UpdateEmployee 1, ‘Jerry’, 26, 32000

Deleting data from a table: This stored procedure deletes an existing employee record from a table named Employee:

CREATE PROCEDURE DeleteEmployee
@EmpCode int
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Employee
WHERE EmpCode = @EmpCode;
END

On executing the above snippet, a stored procedure to delete the details in the table will be created.

Now, by calling the below query, the data with “EmpCode=1” in the table can be deleted

EXEC DeleteEmployee 1

These are just a few examples, and there are many more possibilities for what you can do with stored procedures.

Maria academy