SQL Server Insert, Retrieve, Update, Delete Operations using Stored Procedures

Estimated study time: 16 minutes. Wrap every CRUD operation in a reusable stored procedure.

Stored procedures wrap SQL logic into a named, reusable object. Instead of sending raw INSERT/SELECT/UPDATE/DELETE statements from your application every time, you call a procedure and pass parameters.

Insert

CREATE PROCEDURE usp_InsertEmployee
    @EmpName VARCHAR(100),
    @DeptID INT
AS
BEGIN
    INSERT INTO Employees (EmpName, DeptID)
    VALUES (@EmpName, @DeptID);
END;

Retrieve

CREATE PROCEDURE usp_GetEmployee
    @EmpID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmpID = @EmpID;
END;

Update

CREATE PROCEDURE usp_UpdateEmployee
    @EmpID INT,
    @EmpName VARCHAR(100)
AS
BEGIN
    UPDATE Employees
    SET EmpName = @EmpName
    WHERE EmpID = @EmpID;
END;

Delete

CREATE PROCEDURE usp_DeleteEmployee
    @EmpID INT
AS
BEGIN
    DELETE FROM Employees WHERE EmpID = @EmpID;
END;

Calling a Procedure

EXEC usp_InsertEmployee @EmpName = 'Ravi Kumar', @DeptID = 3;