MSSQLTips

Hi,

I have created a stored procedure which will delete a particular record in a table based on the input we give as a parameter. It is working fine.  Here is the following code

Code
-------
Create procedure [dbo].[emp_insert]
@Name nvarchar(15),
@Age int,
@DOB datetime,
@BGRP nchar(10)
as
begin
insert into Employee values(@Name,@Age,@DOB,@BGRP)
end

Now what i need is a small modification in the stored procedure. Before deleting the record in the table, it should get copied in a new table which has the same schema. Only after inserting the record in the new table, the existing record in the old table should get deleted. Pls give me the modification in the above coding.

It is very urgent

Pls help

Here's a stab..... this is untested so test it out.  I don't know your schema so I made some assumptions 

 create procedure dbo.emp_insert @Name nvarchar(15),
                                @Age int,
                                @DOB datetime,
                                @BGRP nchar(10)
as

set nocount on
set xact_abort on

begin tran tran1

insert into dbo.Employee_History ([Name],Age,DOB,BGRP) values (@Name,@Age,@DOB,@BGRP)
delete from dbo.Employee where [Name] = @Name

commit

go
 

Hi Rajan, Instead of writing code. Why cant you use triggers. when you delete the record trigger automatically insert the old value into another table. Thanx, Karthik