Stored procedure for insert, update, delete, Select and search operation in table In SQL Server

What is Stored Procedure?

A stored procedure is a pre-compiled group of Transact-SQL statements. A stored procedure is a group of SQL statements that has been created and stored in the database . Stored procedure will reduce network traffic and increase the performance.

It is always recommended to create Stored Procedure instead of writing Inline queries so that we can just call the Stored Procedures whenever required instead of writing Inline queries each time.

Let's create a table and the basic stored procedure to perform Save, Update, Delete, Select and Search operation on SQL server database table.

First of all create a database in SQL server and name it "Emp_Table" or whatever you want.

Column Name	Data Type
Emp_Id_Pk	Int(Primary Key and set Is Identity=true)
Emp_Name	varchar(100)
Age	int

Create a table with the columns and data type as shown above and name it " Emp_Table" using the script below.

CREATE TABLE [dbo].[Emp_Table]

(

                [Emp_Id_Pk]                             [int]       IDENTITY(1,1) NOT NULL,

                [Emp_Name]                     [varchar](100) NULL,

                [Age]                              [Int],

)

1.     Stored procedure to insert Employee details in  Emp_Table  

CREATE PROCEDURE [dbo].[InsertEmp_Sp]

                @Emp_Name                    VARCHAR(100),

                @Age                             int

                AS

BEGIN 

                INSERT INTO Emp_Table 

                                (Emp_Name,Age)

                VALUES

                                (@Emp_Name,@Age) 

END

2. Stored procedure to update Employee details in Emp_Table

CREATE PROCEDURE [dbo].[UpdateEmpRecords_Sp]              

                (

                                @EmpId                            INT,

                               @Emp_Name                    VARCHAR(100),

                                   @Age                             int

                )             

AS

BEGIN

                UPDATE Emp_table  SET

                                Emp_Name=@Emp_Name,

                                Age=@Age

                WHERE Emp_Id_Pk=@EmpId

END

3. Stored procedure to delete employee details in Emp_Table  

CREATE PROCEDURE [dbo].[DeleteEmpRecords_Sp]

                (

                                @EmpId            INT

                )             

AS

BEGIN

                DELETE FROM Emp_table WHERE Emp_Id_Pk=@EmpId

END

4. Stored procedure to Select records from Emp_Table  to bind in any data control e.g. GridView, DataList, Repeater etc.

CREATE PROCEDURE [dbo].[BindEmpDetails_Sp]      

AS

BEGIN 

                                SELECT * FROM Emp_Table

END

5.  Stored procedure to search any Employee based on Emp ID from Emp_Table  

CREATE PROCEDURE [dbo].[SearchEmpRecord_Sp] 

                (

                                @EmpId int

                )

AS

SELECT * FROM Emp_table WHERE Emp_Id_Pk=@EmpId