We can create temporary table(s) using stored procedure for intermediate data manipulation. Below code is used to remove duplicate records from tables using procedure.
CREATE TABLE Employee_Test
(
Emp_ID int identity, Emp_Name varchar (55) NULL,
Emp_Sal decimal (10, 2) NULL,
Emp_Designation varchar (20) NULL
)
Go
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES(‘Amit’,12000,’SE’)
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES(‘Amit’,12000,’SE’)
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES(‘Mohan’,15000,’SE’)
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES(‘Mohan’,15000,’SE’)
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES(‘Riyaz’,16000,’SE’)
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES(‘Monu’,27000,’SSE’)
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES(‘Amit’,12000,’SE’)
GO
–Now see table
SELECT * FROM dbo.Employee_Test
create procedure usp_Removedup
as
begin
CREATE TABLE #tempEmpdata
(
Emp_Name varchar (55) NULL,
Emp_Sal decimal (10, 2) NULL,
Emp_Designation varchar (20) NULL
)
–Identify and save distinct data into temporary table
INSERT INTO #tempEmpdata SELECT DISTINCT Emp_Name,Emp_Sal, Emp_Designation FROM Employee_Test
–Delete original table data by using Truncate command, since it has Emp_ID as identiy col
TRUNCATE table Employee_Test
–Now insert data from temp table to original table
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation) SELECT Emp_Name,Emp_Sal,Emp_Designation FROM #tempEmpdata
–Now drop temp table
drop TABLE #tempEmpdata
end
–Execute above created procedure to remove duplicate records
exec usp_Removedup
–now see original table
Select * from Employee_Test
Insert Operation
We can insert records into the table(s) using stored procedure by passing data in input parameters. Below code is used to insert record in the table “Employee” using stored procedure
CREATE TABLE Employee
(
EmpID int primary key, Name varchar(50),
Salary int,
Address varchar(100)
)
Insert into Employee(EmpID,Name,Salary,Address) Values(1,’Mohan’,16000,’Delhi’)
Insert into Employee(EmpID,Name,Salary,Address) Values(2,’Asif’,15000,’Delhi’)
Insert into Employee(EmpID,Name,Salary,Address) Values(3,’Bhuvnesh’,19000,’Noida’)
–See table
SELECT * FROM Employee
CREATE PROCEDURE usp_InsertEmployee
@flag bit output,– return 0 for fail,1 for success
@EmpID int,
@Name varchar(50),
@Salary int,
@Address varchar(100)
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
Insert into Employee(EmpID,Name,Salary,Address) Values(@EmpID,@Name,@Salary,@Address)
set @flag=1;
IF @@TRANCOUNT > 0
BEGIN commit TRANSACTION;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN rollback TRANSACTION;
END
set @flag=0;
END CATCH
END
–Execute above created procedure to insert rows into table
Declare @flag bit
EXEC usp_InsertEmployee @flag output,1,’Deepak’,14000,’Noida’
if @flag=1
print ‘Successfully inserted’
else
print ‘There is some error’
–Execute above created procedure to insert rows into table
Declare @flag bit
EXEC usp_InsertEmployee @flag output,4,’Deepak’,14000,’Noida’
if @flag=1
print ‘Successfully inserted’
else
print ‘There is some error’
–now see modified table
Select * from Employee
Retrieve Operation
We can retrieve data from one or more tables/views with the help of join, using stored procedure. We can put multiple sql statements with in a single stored procedure. Below code is used to fetch data from a table “Employee” using stored procedure
— first we Insert data in the table
Insert into Employee(EmpID,Name,Salary,Address) Values(1,’Mohan’,16000,’Delhi’)
Insert into Employee(EmpID,Name,Salary,Address) Values(2,’Asif’,15000,’Delhi’)
Insert into Employee(EmpID,Name,Salary,Address) Values(3,’Bhuvnesh’,19000,’Noida’)
go
–Now we create a procedure to fetch data
CREATE PROCEDURE usp_SelectEmployee
As
Select * from Employee ORDER By EmpID
–Execute above created procedure to fetch data
exec usp_SelectEmployee
Update Operation
We can update records of the table(s) using stored procedure by passing data in input parameters. Below code is used to update a table “Employee” using stored procedure
CREATE PROCEDURE usp_UpdateEmployee
@flag bit output,– return 0 for fail,1 for success
@EmpID int,
@Salary int,
@Address varchar(100)
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
Update Employee set Salary=@Salary, Address=@Address
Where EmpID=@EmpID
set @flag=1;
IF @@TRANCOUNT > 0
BEGIN commit TRANSACTION;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN rollback TRANSACTION;
END
set @flag=0;
END CATCH
END
–Execute above created procedure to update table
Declare @flag bit
EXEC usp_UpdateEmployee @flag output,1,22000,’Noida’
if @flag=1 print ‘Successfully updated’
else
print ‘There is some error’
–now see updated table
Select * from Employee
Delete Operation
We can delete records of the table(s) using stored procedure by passing data in input parameters. Below code is used to update a table “Employee” using stored procedure
CREATE PROCEDURE usp_DeleteEmployee
@flag bit output,– return 0 for fail,1 for success
@EmpID int
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
Delete from Employee Where EmpID=@EmpID set @flag=1;
IF @@TRANCOUNT > 0
BEGIN commit TRANSACTION;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN rollback TRANSACTION;
END
set @flag=0;
END CATCH
END
–Execute above created procedure to delete rows from table
Declare @flag bit
EXEC usp_DeleteEmployee @flag output, 4
if @flag=1
print ‘Successfully deleted’
else
print ‘There is some error’
–now see modified table
Select * from Employee
We can create temporary table(s) using stored procedure for intermediate data manipulation. Below code is used to remove duplicate records from tables using procedure.
CREATE TABLE Employee_Test
(
Emp_ID int identity, Emp_Name varchar (55) NULL,
Emp_Sal decimal (10, 2) NULL,
Emp_Designation varchar (20) NULL
)
Go
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES(‘Amit’,12000,’SE’)
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES(‘Amit’,12000,’SE’)
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES(‘Mohan’,15000,’SE’)
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES(‘Mohan’,15000,’SE’)
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES(‘Riyaz’,16000,’SE’)
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES(‘Monu’,27000,’SSE’)
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES(‘Amit’,12000,’SE’)
GO
–Now see table
SELECT * FROM dbo.Employee_Test
create procedure usp_Removedup
as
begin
CREATE TABLE #tempEmpdata
(
Emp_Name varchar (55) NULL,
Emp_Sal decimal (10, 2) NULL,
Emp_Designation varchar (20) NULL
)
–Identify and save distinct data into temporary table
INSERT INTO #tempEmpdata SELECT DISTINCT Emp_Name,Emp_Sal, Emp_Designation FROM Employee_Test
–Delete original table data by using Truncate command, since it has Emp_ID as identiy col
TRUNCATE table Employee_Test
–Now insert data from temp table to original table
INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation) SELECT Emp_Name,Emp_Sal,Emp_Designation FROM #tempEmpdata
–Now drop temp table
drop TABLE #tempEmpdata
end
–Execute above created procedure to remove duplicate records
exec usp_Removedup
–now see original table
Select * from Employee_Test
Insert Operation
We can insert records into the table(s) using stored procedure by passing data in input parameters. Below code is used to insert record in the table “Employee” using stored procedure
CREATE TABLE Employee
(
EmpID int primary key, Name varchar(50),
Salary int,
Address varchar(100)
)
Insert into Employee(EmpID,Name,Salary,Address) Values(1,’Mohan’,16000,’Delhi’)
Insert into Employee(EmpID,Name,Salary,Address) Values(2,’Asif’,15000,’Delhi’)
Insert into Employee(EmpID,Name,Salary,Address) Values(3,’Bhuvnesh’,19000,’Noida’)
–See table
SELECT * FROM Employee
CREATE PROCEDURE usp_InsertEmployee
@flag bit output,– return 0 for fail,1 for success
@EmpID int,
@Name varchar(50),
@Salary int,
@Address varchar(100)
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
Insert into Employee(EmpID,Name,Salary,Address) Values(@EmpID,@Name,@Salary,@Address)
set @flag=1;
IF @@TRANCOUNT > 0
BEGIN commit TRANSACTION;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN rollback TRANSACTION;
END
set @flag=0;
END CATCH
END
–Execute above created procedure to insert rows into table
Declare @flag bit
EXEC usp_InsertEmployee @flag output,1,’Deepak’,14000,’Noida’
if @flag=1
print ‘Successfully inserted’
else
print ‘There is some error’
–Execute above created procedure to insert rows into table
Declare @flag bit
EXEC usp_InsertEmployee @flag output,4,’Deepak’,14000,’Noida’
if @flag=1
print ‘Successfully inserted’
else
print ‘There is some error’
–now see modified table
Select * from Employee
Retrieve Operation
We can retrieve data from one or more tables/views with the help of join, using stored procedure. We can put multiple sql statements with in a single stored procedure. Below code is used to fetch data from a table “Employee” using stored procedure
— first we Insert data in the table
Insert into Employee(EmpID,Name,Salary,Address) Values(1,’Mohan’,16000,’Delhi’)
Insert into Employee(EmpID,Name,Salary,Address) Values(2,’Asif’,15000,’Delhi’)
Insert into Employee(EmpID,Name,Salary,Address) Values(3,’Bhuvnesh’,19000,’Noida’)
go
–Now we create a procedure to fetch data
CREATE PROCEDURE usp_SelectEmployee
As
Select * from Employee ORDER By EmpID
–Execute above created procedure to fetch data
exec usp_SelectEmployee
Update Operation
We can update records of the table(s) using stored procedure by passing data in input parameters. Below code is used to update a table “Employee” using stored procedure
CREATE PROCEDURE usp_UpdateEmployee
@flag bit output,– return 0 for fail,1 for success
@EmpID int,
@Salary int,
@Address varchar(100)
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
Update Employee set Salary=@Salary, Address=@Address
Where EmpID=@EmpID
set @flag=1;
IF @@TRANCOUNT > 0
BEGIN commit TRANSACTION;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN rollback TRANSACTION;
END
set @flag=0;
END CATCH
END
–Execute above created procedure to update table
Declare @flag bit
EXEC usp_UpdateEmployee @flag output,1,22000,’Noida’
if @flag=1 print ‘Successfully updated’
else
print ‘There is some error’
–now see updated table
Select * from Employee
Delete Operation
We can delete records of the table(s) using stored procedure by passing data in input parameters. Below code is used to update a table “Employee” using stored procedure
CREATE PROCEDURE usp_DeleteEmployee
@flag bit output,– return 0 for fail,1 for success
@EmpID int
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
Delete from Employee Where EmpID=@EmpID set @flag=1;
IF @@TRANCOUNT > 0
BEGIN commit TRANSACTION;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN rollback TRANSACTION;
END
set @flag=0;
END CATCH
END
–Execute above created procedure to delete rows from table
Declare @flag bit
EXEC usp_DeleteEmployee @flag output, 4
if @flag=1
print ‘Successfully deleted’
else
print ‘There is some error’
–now see modified table
Select * from Employee