在sql server 2005中创建插入和更新的存储过程 [英] Creating stored procedure for insert and update in sql server 2005

问题描述

Hello All，



我想在sql server 2005中创建程序..插入和更新记录。

i需要拿从第一个表（比如员工表）的现有记录

到第二个表（比如备份表）和

之后我必须在第一个表中存储新的更新记录（即员工表）

这样我们怎样才能使用存储过程来完成任务..

如果知道的话请帮我解决..



谢谢，

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,

推荐答案

尝试以下方法。

首先我们检索员工记录将在Employee表中更新并将其插入备份表。然后更新Employee表中的员工记录。此处存储过程仅更新员工姓名和电子邮件地址。您可以在存储过程中更新所需的任何其他Employee字段。

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        

设置ANSI_NULLS ON

设置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 ='插入'

BEGIN

INSERT INTO stud（name1，class，add1）VALUES（@ name，@ class，@ add）

- 插入员工（id，first_name，last_name，salary，city）值（@ id，@ first_name，@ original_name，@ salary，@city）

结束



如果@StatementType ='选择'

BEGIN

select * from stud

结束



如果@StatementType ='更新'

BEGIN

UPDATE stud SET

name1 = @ name，class = @class，add1 = @add

WHERE name1 = @name

END



else IF @StatementType ='删除'

BEGIN

DELETE FROM stud WHERE name1 = @name

END

END

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

这篇关于在sql server 2005中创建插入和更新的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！