如何在同一个存储过程中对单个表使用多个更新查询

I have stored procedure which has

我有存储过程

1) update query and I want to add the

1)更新查询,我想添加

2) update query in the same store procedure. Is that possible that I can use the below code? or Can anyone help me how can I use multiple update query for the same table?

2)在同一商店程序中更新查询。我可以使用下面的代码吗?或者任何人都可以帮助我如何在同一个表中使用多个更新查询?

USE [Databse]
GO
/****** Object:  StoredProcedure [dbo].[sp_Tableupdate]    
Script Date: 5/19/2017 8:12:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Tableupdate]
@IncomeID int,
@MemberID int,
@ParticipantID int,
@IncomeTypeID int,
@PaymentFrequencyTypeID int,
@Employer varchar(max),
@Occupation varchar(max),
@TypeOfBusiness varchar(max),
@GrossAmount decimal(18,2),
@Verified bit
AS
BEGIN
1)UPDATE Table SET
    MemberID=@MemberID,
    ParticipantID=@ParticipantID,
    IncomeTypeID=@IncomeTypeID,
    PaymentFrequencyTypeID=@PaymentFrequencyTypeID,
    Employer=@Employer,
    Occupation=@Occupation,
    TypeOfBusiness=@TypeOfBusiness,
    GrossAmount=@GrossAmount,
    Verified=@Verified 
WHERE IncomeID=@IncomeID

2)Update table set ParticipantID = @ParticipantID where MemberID = @MemberID
END

1 个解决方案

#1

This is definitely possible, you can set as many UPDATE statements in their as you want.

这绝对是可能的,您可以根据需要在其中设置尽可能多的UPDATE语句。

Do note that your last update statement will overwrite your first update statements, if the same records are touched.

请注意,如果触及相同的记录,您的上一个更新语句将覆盖您的第一个更新语句。

Best practice would be to end each statement with a semicolon e.g. ';').

最佳做法是用分号结束每个语句,例如: ';')。

USE [Databse]
GO
/****** Object:  StoredProcedure [dbo].[sp_Tableupdate]    
Script Date: 5/19/2017 8:12:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Tableupdate]
@IncomeID int,
@MemberID int,
@ParticipantID int,
@IncomeTypeID int,
@PaymentFrequencyTypeID int,
@Employer varchar(max),
@Occupation varchar(max),
@TypeOfBusiness varchar(max),
@GrossAmount decimal(18,2),
@Verified bit,
@run_proc int
AS
BEGIN
IF(@run_proc = 1)
BEGIN
PRINT 'Running Update 1';
UPDATE Table SET
    MemberID=@MemberID,
    ParticipantID=@ParticipantID,
    IncomeTypeID=@IncomeTypeID,
    PaymentFrequencyTypeID=@PaymentFrequencyTypeID,
    Employer=@Employer,
    Occupation=@Occupation,
    TypeOfBusiness=@TypeOfBusiness,
    GrossAmount=@GrossAmount,
    Verified=@Verified 
WHERE IncomeID=@IncomeID;
END

ELSE IF(@run_proc = 2) BEGIN
    PRINT 'Running Update 2';
    Update table set ParticipantID = @ParticipantID where MemberID = @MemberID  
END

END

#1