SQL Server Select Insert Update Delete in Single Stored Procedure (Query) with Example

Create Table

CREATE TABLE Employee

(

EmpId INT IDENTITY, 

EmpName VARCHAR(50),

EmpJob  VARCHAR(50),

EmpSalary  Money

)

Now create following stored procedure in your database to perform insert, select, update, delete operations in single stored procedure in SQL server

CREATE PROCEDURE SP_Employee

@EmpId int = 0,

@EmpName varchar(50)=null,

@EmpJob  varchar(50)=null,

@EmpSalary money,

@status varchar(50)

AS

BEGIN

SET NOCOUNT ON;

--- Insert New Records

IF @status='INSERT'

BEGIN

INSERT INTO Employee(EmpId,EmpName,EmpJob,EmpSalary) VALUES(@EmpId,@EmpName,@EmpJob,@EmpSalary)

END

--- Select Records in Table

IF @status='SELECT'

BEGIN

SELECT EmpId,EmpName,EmpJob,EmpSalary FROM Employee

END

--- Update Records in Table

IF @status='UPDATE'

BEGIN

UPDATE Employee SET EmpName=@EmpName,EmpJob=@EmpJob,EmpSalary=@EmpSalary  WHERE EmpId=@EmpId

END

--- Delete Records from Table

IF @status='DELETE'

BEGIN

DELETE FROM Employee  where EmpId=@EmpId

END

SET NOCOUNT OFF

END

Insert Query

To insert data in the newly created Employee  table we need to write the query like as shown below

Exec SP_Employee @EmpName='mithilesh',@EmpJob='Student',@EmpSalary=5000,@status='INSERT'

Select Query

If we want to get data from the Employee table we need to execute the following query

Exec SP_Employee @status='SELECT'

Update Query

If we want to update data in Employee table we need to execute the following query

Exec SP_Employee @EmpId=1, @EmpName='Sanjeev',@EmpJob=hr,@EmpSalary=20000,@status='UPDATE'