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

Introduction:

This article explains how to select, insert, update, delete in one stored procedure in sql server 2008, 2010, 2012 or sql server query to insert, select(retrieve), edit, update, delete operations in single stored procedure with example or sql server insert, update, delete, select using single stored procedure with example.

Before we implement select, insert, update, delete operations in single stored procedure in sql server first we need to design one table “productinfo” in your database for use below script.
---------------------------------------------------------------------------------

 CREATE TABLE ProductInfo
(
ProductId INT IDENTITY, 
Productname VARCHAR(50),
Price INT
)

---------------------------------------------------------------------------------
Once we design the table that would be like as shown below

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

----------------------------------------------------------------------------------------

CREATE PROCEDURE CrudOperations
@productid int = 0,
@productname varchar(50)=null,
@price int=0,
@status varchar(50)
AS
BEGIN
SET NOCOUNT ON;
--- Insert New Records
IF @status='INSERT'
BEGIN
INSERT INTO productinfo(productname,price) VALUES(@productname,@price)
END
--- Select Records in Table
IF @status='SELECT'
BEGIN
SELECT productid,productname,price FROM productinfo
END
--- Update Records in Table
IF @status='UPDATE'
BEGIN
UPDATE productinfo SET productname=@productname,price=@price

WHERE productid=@productid
END
--- Delete Records from Table
IF @status='DELETE'
BEGIN
DELETE FROM productinfo where productid=@productid
END
SET NOCOUNT OFF
END

----------------------------------------------------------------------------------------
Now we will see each operation with example

Insert Query:

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

 Exec Crudoperations @productname='oneplus one',@price=20000,@status='INSERT'

-----------------------------------------------------------------------------------------
Once we execute above query we will get output message like as shown below

Select Query:

If we want to get data from productinfo table we need to execute following query
------------------------------------------------------------------------------------------

Exec Crudoperations @status='SELECT'

------------------------------------------------------------------------------------------
When we execute above query we will get following output

Update Query:

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

------------------------------------------------------------------------------------------

Exec Crudoperations @productid=1, @productname='oneplus two',@price=28550,@status='UPDATE'

------------------------------------------------------------------------------------------

Once we execute above query we will get output message like as shown below

Now if we want to check productinfo table data we need to execute following query

----------------------------------------------------------------------------------------------

Exec Crudoperations @status='SELECT'

----------------------------------------------------------------------------------------------
When we execute above query we will get output with updated data

Delete Query:

If we want to delete data from productinfo table we need to execute following query
----------------------------------------------------------------------------------------------

Exec Crudoperations @productid=1,@status='DELETE'

----------------------------------------------------------------------------------------------
Once we execute above query we will get output message like as shown below

Now if we want to check productinfo table data we need to execute following query

-----------------------------------------------------------------------------------------------