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

By
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:-
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...

0 comments:

Post a Comment

Powered by Blogger.