How to Perform Some DML Commands with Single Stored Procedure in SQL Server

Introduction:-  There are two types of statements used in SQL Server 
1.) DML(Data Manipulation Language):- Some Commands of DML are:

• SELECT :-Retrieve information from the database.
• INSERT:-Insert data into a table.
• UPDATE:-Update Existing Records in a table.
• DELETE:- Delete Records in a table.

2.) DDL (Data Definition Language) :- There are some Commands of DDL are:-

• CREATE:-Create table in database.
• ALTER:-Modify the structure of database.
• DROP:- Delete the table in database.
• TRUNCATE:-Remove all records from the table including  all allotted space also.
• COMMENT:- It is used for comment.
• RENAME:- Rename the objects.

Here we will perform select, insert,Update and Delete commands in a single Stored Procedure in sql server.

You can learn more about sql server from below links:-

• Learn Stored procedure in sql server
• Difference between stored procedure and function

There are some steps to implement this whole concepts as given below:-
Step 1 :- First open Open SQL Server Management Studio-->Create a table (Student_Details) with an  identity column as given below:-

CREATE TABLE Student_Details
 (
 St_Id INT IDENTITY,
 St_Name varchar(40),
 St_Age INT,
 St_College varchar(50)
 )

Step 2 :- Create Stored procedure  (Some_Operation1) and Run it as shown below:-

CREATE PROCEDURE Some_Operation1
 @St_Id int = 0,
 @St_Name varchar(40)=null,
 @St_Age int =0,
 @St_College varchar(50)=null,
 @St_Operation varchar(50)
 AS
 BEGIN
 SET NOCOUNT ON;
 --Insert new values in Student_Details table
 IF @St_Operation='INSERT'
 BEGIN
 INSERT INTO Student_Details(St_Name,St_Age,St_College) VALUES(@St_Name,@St_Age,@St_College)
 END
 -- Select Records in Table
 IF @St_Operation='SELECT'
 BEGIN
 SELECT ST_id,St_Name,St_Age,St_College FROM Student_Details
 END
 --- Update Records in Table
 IF @St_Operation='UPDATE'
 BEGIN
 UPDATE Student_Details SET St_Name=@St_Name,St_Age=@St_Age,St_College=@St_College WHERE St_Id=@St_Id
 END
 --- Delete Records from Table
 IF @St_Operation='DELETE'
 BEGIN
 DELETE FROM Student_Details where St_Id=@St_Id
 END
 SET NOCOUNT OFF
  END

Run it:-

Step 3 :- Now INSERT some Value in Student_Details table as shown below:-

Execute Some_Operation1 @St_Name='Neha Singh',@St_Age=25,@St_College='INDIAN Public School',@St_Operation='INSERT'

Step 3 :-Now use SELECT statement in  Student_Details table as shown below:-

Execute Some_Operation1 @St_Operation='SELECT'

Step 4 :-Now UPDATE the Student_Details table data as shown below:-

Execute Some_Operation1 @St_Id=1,@St_Name='Himanshu',@St_Age=26,@St_College='Lucknow Public School',@St_Operation='UPDATE'

Step 5 :-Now DELETE the Student_Details table records as shown below:-

Execute Some_Operation1 @St_Id=3,@St_Operation='DELETE'

For More...

• Learn .NET Interview Questions and answers for job
• How to take print receipt in windows forms Application
• How to print Gridview data using c#
• How to implement 3 tier Architecture in asp.net application with examples
• How to create secure login page in asp.net with examples