How to create Store Procedure in Sql Server to Insert,Update and delete? - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework 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.

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon