How to create Store Procedure in Sql Server to Insert,Update and delete? | ASPMANTRA | Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server AND WCF snippets and tutorial

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.

Post a Comment

[blogger]

MKRdezign

Contact Form

Name

Email *

Message *

Powered by Blogger.
Javascript DisablePlease Enable Javascript To See All Widget