如何使用存储过程在事务中插入，更新，删除和选择 [英] how to do insert,update,delete and select in transactions using stored procedure

问题描述

请问有人告诉我如何使用存储过程在事务中进行插入，更新，删除和选择操作。请告诉我们是否有可能使用触发器。请帮助我，我是新手。

Pls some one tell that how to do insert,update,delete and select operation in transaction using stored procedure.Pls some one tell if possible with trigger also. Pls help me i am new to this.

推荐答案

你好Chander_rani，



在SQL Stroed Proc u可以做很多事情。以下链接可能对您有所帮助。



使用-存储过程合SQL UPDATE-DELETE-http://www.c-sharpcorner.com/UploadFile/rohatash/select-insert-update-delete-using-stored-procedure-in-sql/ [ ^ ]



问候，

Babu.K

Hi Chander_rani,

In SQL Stroed Proc u can do lots of stuff. The below link might be helpful for u.

http://www.c-sharpcorner.com/UploadFile/rohatash/select-insert-update-delete-using-stored-procedure-in-sql/[^]

Regards,
Babu.K

BEGIN TRAN Atran



你的sql





如果@ @ERROR<> 0

ROLLBACK Atran

否则提交Atran

BEGIN TRAN Atran

your sql


if @@ERROR <> 0
ROLLBACK Atran
else commit Atran

你可以这样做，



创建PROCEDURE MasterInsertUpdateDelete

（

@id INTEGER，

@first_name VARCHAR（10），

@last_name VARCHAR（10），

@salary DECIMAL（10,2），

@city VARCHAR（20），

@ StatementType nvarchar（20）=''''

）



AS





BEGIN

BEGIN TRAN



IF @StatementType =''插入''

BEGIN

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

结束



如果@StatementType =''选择''

BEGIN

select * from employee

END



IF @StatementType =''更新''

BEGIN

UPDATE员工SET

First_name = @ first_name，last_name = @last_name，salary = @salary，

city = @city

WHERE id = @ id

END



else IF @StatementType =''删除''

BEGIN

DELETE FROM employee WHERE id = @id

END

IF @@ ERROR<> 0

ROLLBACK TRAN

ELSE COMMIT TRAN

END



问候，

Babu.K

You Can do like that,

Create PROCEDURE MasterInsertUpdateDelete
(
@id INTEGER,
@first_name VARCHAR(10),
@last_name VARCHAR(10),
@salary DECIMAL(10,2),
@city VARCHAR(20),
@StatementType nvarchar(20) = ''''
)

AS


BEGIN
BEGIN TRAN

IF @StatementType = ''Insert''
BEGIN
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 employee
END

IF @StatementType = ''Update''
BEGIN
UPDATE employee SET
First_name = @first_name, last_name = @last_name, salary = @salary,
city = @city
WHERE id = @id
END

else IF @StatementType = ''Delete''
BEGIN
DELETE FROM employee WHERE id = @id
END
IF @@ERROR <> 0
ROLLBACK TRAN
ELSE COMMIT TRAN
END

Regards,
Babu.K

这篇关于如何使用存储过程在事务中插入，更新，删除和选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！