How to create Store Procedure in Sql Server to Insert,Update and delete?

Introduction: In this post I will explain you how to create Store Procedure to Insert, Delete and Update the data in table.

Description: I have Create a Database name TEST_DATABASE and also a table name DEPARTMENT_EMPLOYEE.

 After go to Programmability>Store Procedures> (right click on Store Procedures)>New Store Procedure.

Store Procedure to Insert Data into Database table:

CREATE PROCEDURE [dbo].[INSERT_EMPLOYEE_DETAIL]

(

@DEPARTMENT_NAME VARCHAR(50),

@EMPLOYEE_NAME VARCHAR(50),

@EMPLOYEE_DESIGNATION VARCHAR(50)

)

AS

BEGIN

     

      SET NOCOUNT ON;

INSERT INTO dbo.DEPARTMENT_EMPLOYEE(DEPARTMENT_NAME,EMPLOYEE_NAME,EMPLOYEE_DESIGNATION) VALUES(@DEPARTMENT_NAME,@EMPLOYEE_NAME,@EMPLOYEE_DESIGNATION)

END

Store Procedure to Update the Table Data:

 CREATE PROCEDURE [dbo].[UPDATE_EMPLOYEE_DETAIL]

(

@DEPARTMENT_ID INT,

@DEPARTMENT_NAME VARCHAR(50),

@EMPLOYEE_NAME VARCHAR(50),

@EMPLOYEE_DESIGNATION VARCHAR(50)

)

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

   UPDATE dbo.DEPARTMENT_EMPLOYEE SET @DEPARTMENT_NAME=@DEPARTMENT_NAME,EMPLOYEE_NAME=@EMPLOYEE_NAME,EMPLOYEE_DESIGNATION=@EMPLOYEE_DESIGNATION

   WHERE DEPARTMENT_ID = @DEPARTMENT_ID

END

Store Procedure to Delete table Data:

CREATE PROCEDURE [dbo].[DELETE_EMPLOYEE_DETAIL]

(

@DEPARTMENT_ID INT

)

AS

BEGIN

     

      SET NOCOUNT ON;

   DELETE FROM dbo.DEPARTMENT_EMPLOYEE WHERE DEPARTMENT_ID=@DEPARTMENT_ID

END

Create store, use in Visual Studio and check out the result.