SQL 一个 StoredProcedure 中的多个更新语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/767574/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple update statements in one StoredProcedure
提问by The real napster
I am wondering if it is possible to have multiple Update statements in a store procedure
我想知道是否可以在存储过程中有多个 Update 语句
Something like this:
像这样的东西：
Update Table1 set field1 = @new_value where id = @table1_id
Update Table2 set field2 = @new_value where id = @table2_id
Update Table3 set field3 = @new_value where id = @table3_id
Right now I am executing them seperately but as they are only used together I wonder if they could be located in just one SP.
现在我正在单独执行它们，但由于它们仅一起使用，我想知道它们是否可以仅位于一个 SP 中。
回答by Quassnoi
Yes, it's possible:
是的，这是可能的：
CREATE PROCEDURE prc_update (@table1_id INT, @table2_id INT, @table3_id INT, @new_value INT)
AS
BEGIN
UPDATE Table1
SET field1 = @new_value
WHERE id = @table1_id
UPDATE Table2
SET field2 = @new_value
WHERE id = @table2_id
UPDATE Table3
SET field3 = @new_value
WHERE id = @table3_id
END
回答by Guffa
Yes, that works fine.
是的，这很好用。
Also put this in the stored procedure before the updates:
在更新之前也把它放在存储过程中：
set nocount on
This keeps the stored procedures from creating result sets for queries without a result. Otherwise each update will produce an empty result set that is sent back to the client.
这可以防止存储过程为没有结果的查询创建结果集。否则每次更新都会产生一个空的结果集，并发送回客户端。
回答by HLGEM
You should wrap those statments in transactions as well so that if one fails all are rolled back.
您还应该将这些语句包装在事务中，以便如果失败，所有语句都将回滚。
回答by karthik kasubha
Below is the stored procedure with transaction , nocounton and multiple update queries.
下面是带有事务、nocounton 和多个更新查询的存储过程。
CREATE PROCEDURE prc_update (@table1_id INT, @table2_id INT, @table3_id INT,
@new_value INT)
AS
BEGIN
BEGIN TRY
Set Nocount ON
Begin Transaction
Save Transaction BeforeTransactionSavePoint
UPDATE Table1
SET field1 = @new_value
WHERE id = @table1_id
UPDATE Table2
SET field2 = @new_value
WHERE id = @table2_id
UPDATE Table3
SET field3 = @new_value
WHERE id = @table3_id
Commit Transaction
END TRY
BEGIN CATCH
If @@TRANCOUNT > 0
Rollback Tran BeforeTransactionSavePoint
DECLARE
@ErMessage NVARCHAR(2048),
@ErSeverity INT,
@ErState INT
SELECT
@ErMessage = ERROR_MESSAGE(),
@ErSeverity = ERROR_SEVERITY(),
@ErState = ERROR_STATE()
RAISERROR (@ErMessage,@ErSeverity,@ErState )
END
