Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

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

Jul 19, 2015
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:

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

create new table in sql server with primary key identity column
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

get data from table in sql server
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

update query in stored procedure in sql server
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

Delete Query in stored procedure in SQL Server

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 RSS subscribe by email Subscribe by Email

10 comments :

Unknown said...

Gud one.it is really helpful.
But how do we select operation in cs page?
Thanks in advance.

Unknown said...

Good sir
But how to write code in codebehind page
its means .aspx.cs

RAJESH KUMAR SAHOO said...
This comment has been removed by the author.
RAJESH KUMAR SAHOO said...

Hello sir, Whenever i execute the create procedure statement i face an error (SET NOCOUNT ON;) on this line
,please sir solve it.

Anonymous said...

Its not working for me..
Uncaught syntax error

Unknown said...

every executing command says expecting parameter productid..

Unknown said...

Thanks very much

Unknown said...

its good to understand for the beginners...

Unknown said...

nice article. Thanks

Fahim Ahmed said...

How to use behind code in c#?

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.