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

Introduction:

Here I will explain 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.

Description:

In previous articles I explained sql server difference b/w @@identity, scope_identity() and current_identity, sql server difference b/w dml, dcl, ddl and tcl statements, find hierarchical data in sql server, cursor example in sql server,  joins in sql server, function example in sql server 2008, Primary key constraint in sql server, foreign key constraint in sql server, query to remove first and last characters from string in sql server and many articles relating to SQL server. Now I will explain how to do select, insert, update, delete in single stored procedure in sql server.

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

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

Output

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

Exec Crudoperations @status='SELECT'

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

Output

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email