Better Microsoft SQL update

Question 1

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 executionDECLARE @DTE AS DATETIMESET @DTE       = GETDATE()INSERT INTO [dbo].[ProcedureExecutionLog]  SELECT @DTE AS Dte, 'spThirdParty_UpdateBankInfo' AS [Procedure]--begin-- VariablesDECLARE @idAccount INT-- Set ValueSELECT @idAccount = ccc.dbo.ThirdParty.idAccountFROM ccc.dbo.ThirdPartyWHERE ccc.dbo.ThirdParty.idThirdParty = @nTPID-- Determine New AccountIF @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  ENDELSE  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 ONGOSET QUOTED_IDENTIFIER ONGO-- This routine is used to update the Bank Info for the ThirdParty-- assigned to the given ThirdPartyIDALTER PROCEDURE [dbo].[spThirdParty_UpdateBankInfo](    @nTPID INT = NULL,    @sAcctNum CHAR(10) = NULL,    @sAcctType INT = NULL,    @nBankID INT = NULL,    @sUserLogin varchar(100) = '')AS--log executionDECLARE @DTE AS DATETIMESET @DTE       = GETDATE()INSERT INTO [dbo].[ProcedureExecutionLog]  SELECT @DTE AS Dte, 'spThirdParty_UpdateBankInfo' AS [Procedure]--begin-- VariablesDECLARE @idAccount INT, @oAcctNum varchar(10) = ' ', @oAcctType int, @oBankID int;-- Set ValueSELECT @idAccount = t.idAccountFROM ccc.dbo.ThirdParty tWHERE t.idThirdParty = @nTPID-- Determine New AccountIF @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  ENDELSE  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  ENDdeclare @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.

Question 2

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 tosimply state the task accomplished by the code. Please seeHow do I ask a good question?.

Question 3

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 ast -- good, thoughtp 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 followingyour local (unstated) style guide.

In the python community wepreferto 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 linemakes them visually prominent, as an aid to cognition.Absent an explicit style guide to the contrary, I tendto apply a similar style in other languages, including SQL.

Question 4

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.

J_H 43.3k3 gold badges38 silver badges158 bronze badges · Accepted Answer · 2024-10-22 22:45:12Z

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 ast -- good, thoughtp 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 followingyour local (unstated) style guide.

In the python community wepreferto 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 linemakes them visually prominent, as an aid to cognition.Absent an explicit style guide to the contrary, I tendto apply a similar style in other languages, including SQL.

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.

Movatterモバイル変換

Stack Exchange Network

Better Microsoft SQL update

1 Answer1

style improvements

leading operator

You mustlog in to answer this question.

Related

Hot Network Questions

Subscribe to RSS