使用单个存储的过程更新多个表 [英] Update Multiple tables with Single Stored Procs

问题描述

大家好，

我必须编写一个存储的proc，在其中我需要根据另一个表的结果来更新某些表.让我们以26号收据为例.

我们有以下要求:

1).更新tbla中收据编号为26的单列.
2).从tblb的26号收据中选择多行的列(Invno，Adjamt).
3).更新tblc，其中c.Invno = b.Invno

最好的方法是什么?

问候！
Aman

Hi All,

I have to write a stored proc in which I need to update some table based on another table''s result. Let us take an example with Receipt No. 26.

We have following requirements:

1). Update single column in tbla where Receipt No. 26.
2). Select multiple rows''s columns(Invno,Adjamt) from tblb where Receipt No. 26.
3). Update tblc where c.Invno=b.Invno

What will be the best way to do so?

Regards!
Aman

推荐答案

类似这样的事情...

Something like this...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE  [dbo].[receipts_UpdateReceiptDetails]
	(
		@ReceiptID			INT,
		@SomeNewValue		INT
	)

AS

SET NOCOUNT ON

/*
__________________________________________________________________________________________
Author:			Dylan Morley
Description:		

Created:		
Modification History:	Date		Modified By	Modification
			[dd/mm/yy]	[Name]		[Desc]

__________________________________________________________________________________________
*/

BEGIN TRY
	BEGIN TRANSACTION

	UPDATE
		TBLA
	SET
		WhateverColumn = @SomeNewValue
	Where
		ReceiptNo = @ReceiptID
	
	UPDATE 
		TBLC
	SET
		WhateverColumn = B.Adjamt
	FROM
		TBLC
	JOIN
		(
			SELECT 
				Invno, Adjamt
			FROM	
				tblb 
			Where
				ReceiptNo = @ReceiptID		
		)
	AS 
		B
	ON
		TBLC.Invno = B.InvNo

	COMMIT
	
END TRY
BEGIN CATCH

  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)


END CATCH

-- Return any error codes \ Reset the NOCOUNT property.
RETURN @@ERROR
SET NOCOUNT OFF

希望 ^ ]和
此 [ ^ ]可能会为您提供帮助.

Hope this[^] and
this[^]might help you.

这篇关于使用单个存储的过程更新多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！