Verify SQL UPDATE Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Verify SQL UPDATE Stored Procedure

Author

Topic

kdford10
Starting Member

7 Posts

Posted - 2010-09-13 : 16:01:50

I wanted to receive feedback from my update stored procedure.

CREATE PROCEDURE dbo.usp_update_Branch
(
@LoanOfficerID int
)
AS

DECLARE @BranchName varchar(30);
DECLARE @ManagerOfficerNumber int;
DECLARE @BranchID int;

BEGIN TRANSACTION UpdateBranch;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

UPDATE dbo.[Branch]
SET
BranchName = @BranchName
,ManagerOfficerNumber = @ManagerOfficerNumber
WHERE
dbo.[Branch].BranchID = @BranchID;

SET @BranchID = SCOPE_IDENTITY();

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-13 : 16:09:05

SCOPE_IDENTITY is for INSERTs. Your stored procedure is doing an update. Remove that last SET statement as it isn't needed.

You aren't setting @BranchID before the UPDATE, so your WHERE clause isn't going to find anything. Shouldn't you be using @LoanOfficerID instead? You aren't setting the values for @BranchName or @ManagerOfficerNumber, so your UPDATE isn't going to do what you think.

Also, remove the comments about SET NOCOUNT. You'll have that in every single stored procedure, so it's unnecessary to have that comment in each.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kdford10
Starting Member

7 Posts

Posted - 2010-09-13 : 16:21:11

If I set values for BranchName and ManagerOfficerNumber and update my WHERE clause to dbo.[Branch].LoanOfficerID = @LoanOfficerID, will it update the BranchName and ManagerOfficerNumber by the LoanOfficerID?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-13 : 16:21:42

Please show us a data example of what you want to do.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kdford10
Starting Member

7 Posts

Posted - 2010-09-13 : 18:46:53

I want to Update the Branch table.
Branch table includes: BranchID, BranchName, ManagerOfficerNumber, AddressID
a LoanOfficer table which includes: LoanOfficerID, BranchID, LoanOfficerFirst & Last Name, LoanOfficerNumber.

So what I want to do is update the Branch table. When the LoanOfficerNumber info changes, the Branch will reflect those changes and update the table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-13 : 19:07:55

How will the Branch table know that the LoanOfficerNumber column changed in the LoanOfficer table? And why would the Branch table even need to be modified if the LoanOfficer table changed?

I'm very confused as to what you want. Please post a data example to make this more clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources