Creating stored procedure for insert and update in sql server 2005

Question

1.00/5 (1 vote)

See more:

Hello All,

I want create procedure in sql server 2005..for insert and update record.
i need to take the existing record from first table (say employee table)
to second table (say Backup table) and after
that i have to store the new updated record in the first table (i.e employee table)
like that so how can we achieve that task using stored procedure..
If any know then pls help me out..

Thanks,

Posted 27-Jul-12 5:07am

raj dhakad

Add a Solution

Comments

Sandeep Mewara 27-Jul-12 15:34pm

Here is what is expected of enquirers:
1. TRY first what you want to do! You may find that it's not that hard.
2. Formulate what was done by you that looks like an issue/not working.

Try them and tell if you face issues.
Members will be more than happy to help like this.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

__TR__ · Answer 1 · 2012-07-27T08:01:00

Try the below approach.
Here first we retrieve the employee record that will be updated in Employee table and insert it into the backup table. Then the employee record in Employee table is updated. Here the stored procedure is only updating the employee name and email address. You can update any other Employee fields you want in your stored procedure.

CREATE PROCEDURE usp_UpdateEmployee
@EmployeeID INT,
@EmployeeName VARCHAR(50),
@Email VARCHAR(50)


AS

BEGIN
BEGIN TRY        
  BEGIN TRANSACTION   


	--Retreive the employee that will be updated in employee table and insert into backup table
	INSERT INTO [BackUp]
	(
		EmployeeID,
		EmployeeName,
		Email
	)
	SELECT EmployeeID,EmployeeName, Email FROM Employee WHERE EmployeeID =  @EmployeeID

	--Update the data in employee table for the given EmployeeID
	UPDATE Employee
	SET EmployeeName = @EmployeeName
		Email = @Email
	WHERE EmployeeID =  @EmployeeID
	

                    
  COMMIT TRANSACTION        
 END TRY        
 BEGIN CATCH        
  ROLLBACK TRANSACTION        
  RAISERROR(ERROR_MESSAGE(),
	ERROR_SEVERITY(),
	1,
	ERROR_NUMBER(), 
	ERROR_SEVERITY(),  
	ERROR_STATE(),
	ERROR_PROCEDURE(),
	ERROR_LINE()
	)      
 END CATCH        
END

navanathdivate · Answer 2 · 2013-08-02T01:41:00

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insstud]
@name varchar(500),
@class varchar(500),
@add varchar(500),
@StatementType nvarchar(20)
AS
BEGIN
-- INSERT INTO stud(name1, class, add1) VALUES(@name, @class, @add)

IF @StatementType = 'Insert'
BEGIN
INSERT INTO stud(name1, class, add1) VALUES(@name, @class, @add)
--insert into employee (id,first_name,last_name,salary,city) values( @id, @first_name, @last_name, @salary, @city)
END

IF @StatementType = 'Select'
BEGIN
select * from stud
END

IF @StatementType = 'Update'
BEGIN
UPDATE stud SET
name1 = @name, class = @class, add1 = @add
WHERE name1 = @name
END

else IF @StatementType = 'Delete'
BEGIN
DELETE FROM stud WHERE name1 = @name
END
END

Creating stored procedure for insert and update in sql server 2005

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0