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.
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. |
|||
|
|||
10 comments :
Gud one.it is really helpful.
But how do we select operation in cs page?
Thanks in advance.
Good sir
But how to write code in codebehind page
its means .aspx.cs
Hello sir, Whenever i execute the create procedure statement i face an error (SET NOCOUNT ON;) on this line
,please sir solve it.
Its not working for me..
Uncaught syntax error
every executing command says expecting parameter productid..
Thanks very much
its good to understand for the beginners...
nice article. Thanks
How to use behind code in c#?
Note: Only a member of this blog may post a comment.