SQL Server Select Insert Update Delete in Single Stored Procedure With Example

• Store Procedure is the Collection of structure query Language SQL Server.
• in this tutorial i explain "How to create the Stored Procedure".
• First we will Perform the DML commands .
• we have hour DML Commands they are Insert,Delete,Update and Select
• To create the SP .we can use the any one of the SQL SERVER 2008,2010,2012
• Stored Procedure Reduces the burden on SQL Server 
• Now we start "how to create the SP"

1.First create the data base with the name NTierWebFormdDB as shown below,This is First Step

       

create database NTierWebFormDB

2. Create the table with the name Employee with the Five Fields this is step two

       

create table emp ( EmpId int Not Null Identity(1,1) Primary Key  , Empname varchar(30),  EmpJob varchar(40),  EmpSalary money not null, DeptName varchar(100) )

       

3. For testing purpose test the table by inserting the values as shown below if it works well then go to next step step otherwise check again

       

insert into emp values('Murali','Software','10000','') select *from emp

4. Create the first Store Procedure withe a Name as Shown Below

       

CREATE PROCEDURE sp_Insertemp ( @EmpName VARCHAR(30), @EmpJob VARCHAR(40), @EmpSalary MONEY, @DeptName varchar(100) ) as begin insert into emp(EmpJob,Empname,EmpSalary,DeptName) values(@EmpJob,@EmpName,@EmpSalary,@DeptName) end

5. Create the Delete Store Procedure as Shown below
    

CREATE PROCEDURE sp_DeleteEmp ( @EmpId int ) as begin delete from emp where EmpId=@EmpId; end select *from emp

6. Now create the select Stored Procedure

CREATE PROCEDURE sp_GetEmp ( @EmpId int ) as begin select *from emp where EmpId=@EmpId end

7. Final step is the create the Update stored procedure

       

CREATE PROCEDURE sp_UpdateEmp ( @Empid int, @EmpName VARCHAR(30), @EmpJob VARCHAR(40), @EmpSalary MONEY, @DeptName varchar(100) ) as begin update emp set EmpJob=@EmpJob, Empname=@EmpName, EmpSalary=@EmpSalary, DeptName=@DeptName  where EmpId=@Empid end

If you get any doudths on stored procedure in sql server please comment below and if you have any queries personaly then send the email to battusclasses@gmail.com. Dont shy ask any question through email.
Thanq