Better Microsoft SQL update

Question

I've got the following SQL that I need to update by adding a new parameter:

ALTER PROCEDURE [dbo].[spThirdParty_UpdateBankInfo](@nTPID INT = NULL, @sAcctNum CHAR(10) = NULL, @sAcctType INT = NULL,
 @nBankID INT = NULL)
AS
--log execution
DECLARE @DTE AS DATETIME
SET @DTE = GETDATE()
INSERT INTO [dbo].[ProcedureExecutionLog]
 SELECT @DTE AS Dte, 'spThirdParty_UpdateBankInfo' AS [Procedure]
--begin
-- Variables
DECLARE @idAccount INT
-- Set Value
SELECT @idAccount = ccc.dbo.ThirdParty.idAccount
FROM ccc.dbo.ThirdParty
WHERE ccc.dbo.ThirdParty.idThirdParty = @nTPID
-- Determine New Account
IF @idAccount IS NOT NULL
 BEGIN
 -- Update Account Info
 UPDATE ccc.dbo.BankAccount
 SET ccc.dbo.BankAccount.AccountNumber = CASE WHEN @sAcctNum IS NOT NULL THEN @sAcctNum ELSE ccc.dbo.BankAccount.AccountNumber END
 , ccc.dbo.BankAccount.AccountType = CASE WHEN @sAcctType IS NOT NULL THEN @sAcctType ELSE ccc.dbo.BankAccount.AccountType END
 , ccc.dbo.BankAccount.idBank = CASE WHEN @sAcctNum IS NOT NULL THEN @nBankID ELSE ccc.dbo.BankAccount.idBank END
 WHERE ccc.dbo.BankAccount.idAccount = @idAccount
 END
ELSE
 BEGIN
 -- Insert New Record
 INSERT INTO ccc.dbo.BankAccount(AccountNumber, AccountType, idBank)
 VALUES (@sAcctNum, @sAcctType, @nBankID)
 SET @idAccount = SCOPE_IDENTITY()
 -- Update with new ID
 UPDATE ccc.dbo.ThirdParty
 SET ccc.dbo.ThirdParty.idAccount = @idAccount
 WHERE ccc.dbo.ThirdParty.idThirdParty = @nTPID
 END

Here is my modified SQL:

USE [Claims]
GO
/****** Object: StoredProcedure [dbo].[spThirdParty_UpdateBankInfo] Script Date: 10/22/2024 9:52:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- This routine is used to update the Bank Info for the ThirdParty
-- assigned to the given ThirdPartyID
ALTER PROCEDURE [dbo].[spThirdParty_UpdateBankInfo](
 @nTPID INT = NULL,
 @sAcctNum CHAR(10) = NULL,
 @sAcctType INT = NULL,
 @nBankID INT = NULL,
 @sUserLogin varchar(100) = ''
)
AS
--log execution
DECLARE @DTE AS DATETIME
SET @DTE = GETDATE()
INSERT INTO [dbo].[ProcedureExecutionLog]
 SELECT @DTE AS Dte, 'spThirdParty_UpdateBankInfo' AS [Procedure]
--begin
-- Variables
DECLARE @idAccount INT, @oAcctNum varchar(10) = ' ', @oAcctType int, @oBankID int;
-- Set Value
SELECT @idAccount = t.idAccount
FROM ccc.dbo.ThirdParty t
WHERE t.idThirdParty = @nTPID
-- Determine New Account
IF @idAccount IS NOT NULL
 BEGIN
 -- Update Account Info
 UPDATE ccc.dbo.BankAccount
 SET
 @oAcctNum = AccountNumber,
 @oAcctType = AccountType,
 @oBankID = idBank,
 AccountNumber = ISNULL(@sAcctNum, AccountNumber),
 AccountType = ISNULL(@sAcctType, AccountType),
 idBank = ISNULL(@nBankID, idBank)
 WHERE idAccount = @idAccount
 END
ELSE
 BEGIN
 -- Insert New Record
 INSERT INTO ccc.dbo.BankAccount
 (
 AccountNumber,
 AccountType,
 idBank
 )
 VALUES
 (
 @sAcctNum,
 @sAcctType,
 @nBankID
 )
 SET @idAccount = SCOPE_IDENTITY()
 -- Update with new ID
 UPDATE ccc.dbo.ThirdParty
 SET idAccount = @idAccount
 WHERE idThirdParty = @nTPID
 END
declare @summary varchar(max) = 
 'Claims.dbo.spThirdParty_UpdateBankInfo BankAccount(idAccount=' + LTRIM(@idAccount) + ') ' +
 'AccountNumber (old: [' + LTRIM(@oAcctNum) + '] new: [' + LTRIM(@sAcctNum) + ']) ' +
 'AccountType (old: [' + LTRIM(ISNULL(@oAcctType, ' ')) + '] new: [' + LTRIM(ISNULL(@sAcctType, ' ')) + ']) ' +
 'idBank (old: [' + @oBankID + '] new: [' + @nBankID + ']) ';
INSERT INTO dbo.[History]
(
 [UserID],
 [Time],
 [Reason]
)
VALUES
(
 @sUserLogin,
 GETDATE(),
 @summary
)

I have to modify about 40 stored procedures like this, and I want to make sure that I don't mess anything up.

• BCdotWEB: The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How do I ask a good question?.

Answer 1

style improvements

The revised code introduces a few improvements, beyond adding another parameter.

• One table column per line -- good! It helps to minimize future git diffs due to maintenance edits.
• Aliasing a verbose table name as t -- good, though tp for Third Party might be more readable.
• ISNULL() replaces tedious conditionals -- very good!

We see consistent use of CASE to highlight sql keywords, which aids readability.

leading operator

The @summary string expression is perhaps following your local (unstated) style guide.

In the python community we prefer to start continuation lines with binary operators such as +, rather than putting them at end of previous line. They are important, and putting them at start of line makes them visually prominent, as an aid to cognition. Absent an explicit style guide to the contrary, I tend to apply a similar style in other languages, including SQL.

• jp2code: Thanks. I typically program in C#, so I get a little nervous whenever I make code edits like removing the CASE statements. I didn't know if ISNULL would introduce issues.