SQL DML Stored Procedure for IINSERT, UPDATE, DELETE
Channel: VinayBitsLearning
Date: 2018-11-21

CREATE TABLE emp 
(  
    id INTEGER PRIMARY KEY,  
    first_name VARCHAR(10),  
    last_name VARCHAR(10),  
    salary DECIMAL(10,2),  
    country VARCHAR(20),  
 )
 INSERT INTO emp VALUES (1, 'Harry', 'Potter', 15000, 'India');  
    INSERT INTO emp VALUES(2, 'Jack', 'yoon', 12000, 'Australia');  
 INSERT INTO emp VALUES(3, 'Henna', 'Thomson', 18000, 'USA');  
 INSERT INTO emp VALUES(4, 'Vin', 'Disel', 16000, 'India');  
 INSERT INTO emp VALUES (5, 'Rahul', 'Sheri', 17000, 'India');  


---InserUpdate Stored Procedure--


 create procedure InsertUpdate  
 (  
    @id INTEGER,  
    @first_name VARCHAR(10),  
    @last_name VARCHAR(10),  
    @salary varchar(20),  
    @country VARCHAR(20),  
    @StatementType nvarchar(20) = ''  
 )  
 AS  
 BEGIN  
 IF @StatementType = 'Insert'  
 BEGIN  
 insert into emp(id,first_name,last_name,salary,country) values( @id, @first_name, @last_name, @salary, @country)  
 END  
 IF @StatementType = 'Select'  
 BEGIN  
 select * from emp  
 END  
 IF @StatementType = 'Update'  
 BEGIN  
 UPDATE emp SET  
 First_name = @first_name, last_name = @last_name, salary = @salary,  
 country = @country  
 WHERE id = @id  
 END  
 end  

---Insert Data Using Procedure---
 exec InsertUpdateDelete @id = 6,  
 @first_name = 'Shobit',  
 @last_name = 'Pandey',  
 @salary = 32000,  
 @country = 'Canada',  
 @StatementType = 'Insert'

--Delete Store Procedure--
 create procedure deletedData  
 (  
    @id integer,  
    @StatementType nvarchar(20) = ''  
 ) as begin IF @StatementType = 'Delete' BEGIN  
 DELETE FROM  
 emp  
 WHERE  
 id = @id END end 

--Delete Store Procedure--
 exec deletedData  id='id'