Stored Procedure Clean Up - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure Clean Up

Author

Topic

JTProg
Starting Member

24 Posts

Posted - 2006-09-12 : 15:42:54

I have a stored procedure that will perform an update based off several conditions, primarily the user making the request. The stored procedure is getting toooooooo lengthy and I would like to clean it up, but don't know how else to attempt this with the multiple conditions.

CREATE PROCEDURE dbo.procRejectLineBatch

@BMIBatch nvarchar(8),
@ApprovedBy nvarchar(35),
@Individual nvarchar(20),
@RejectReason nvarchar(100),

@ResultMessage varchar(20) = Null output

AS

Declare @ClosureStatus Nvarchar(25),
@Route Nvarchar(25),
@TC bit

Select @ClosureStatus = Closure, @Route = Route, @TC = TeamCoordinatorYN
FROM dbo.Que
WHERE dbo.Que.BMIBatch = @BMIBatch

If (@ClosureStatus = 'Team Coordinator') AND (@Individual = 'Team Coordinator' AND @Route = 'Team Coordinator')
BEGIN
BEGIN TRAN

UPDATE dbo.Batch

SET ApprovedBy = @ApprovedBy,
ApprovedDate = GetDate(),
Status = 2,
RejectReason = @RejectReason

WHERE
( BMIBatch = @BMIBatch)

UPDATE dbo.Que

SET Status = 2,
Route = 'Adjuster',
TeamCoordinatorYN = 0,
AssociateDirectorYN = 0,
DirectorYN = 0

WHERE
( BMIBatch = @BMIBatch)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return
END

COMMIT TRAN
END

IF (@ClosureStatus = 'Associate Director' OR @ClosureStatus = 'Director') AND (@Individual = 'Team Coordinator' AND @TC = 0 AND @Route = 'Team Coordinator')
BEGIN
BEGIN TRAN

UPDATE dbo.Batch

SET ApprovedBy = @ApprovedBy,
ApprovedDate = GetDate(),
Status = 2,
RejectReason = @RejectReason

WHERE
( BMIBatch = @BMIBatch)

UPDATE dbo.Que

SET Status = 2,
Route = 'Adjuster',
TeamCoordinatorYN = 0,
AssociateDirectorYN = 0,
DirectorYN = 0

WHERE
( BMIBatch = @BMIBatch)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return
END

COMMIT TRAN
END

--Associate Director
If (@ClosureStatus = 'Associate Director' OR @ClosureStatus = 'Team Coordinator') AND (@Individual = 'Associate Director') AND (@Route = 'Team Coordinator' OR @Route = 'Associate Director')
BEGIN
BEGIN TRAN

UPDATE dbo.Batch

SET ApprovedBy = @ApprovedBy,
ApprovedDate = GetDate(),
Status = 2,
RejectReason = @RejectReason

WHERE
( BMIBatch = @BMIBatch)

UPDATE dbo.Que

SET Status = 2,
Route = 'Adjuster',
TeamCoordinatorYN = 0,
AssociateDirectorYN = 0,
DirectorYN = 0

WHERE
( BMIBatch = @BMIBatch)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return
END

COMMIT TRAN
END

If (@ClosureStatus = 'Director') AND (@Individual = 'Associate Director') AND (@Route = 'Team Coordinator' OR @Route = 'Associate Director')
BEGIN
BEGIN TRAN

UPDATE dbo.Batch

SET ApprovedBy = @ApprovedBy,
ApprovedDate = GetDate(),
Status = 2,
RejectReason = @RejectReason

WHERE
( BMIBatch = @BMIBatch)

UPDATE dbo.Que

SET Status = 2,
Route = 'Adjuster',
TeamCoordinatorYN = 0,
AssociateDirectorYN = 0,
DirectorYN = 0

WHERE
( BMIBatch = @BMIBatch)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return
END

COMMIT TRAN
END

If (@Individual = 'Director')
BEGIN
BEGIN TRAN

UPDATE dbo.Batch

SET ApprovedBy = @ApprovedBy,
ApprovedDate = GetDate(),
Status = 2,
RejectReason = @RejectReason

WHERE
( BMIBatch = @BMIBatch)

UPDATE dbo.Que

SET Status = 2,
Route = 'Adjuster',
TeamCoordinatorYN = 0,
AssociateDirectorYN = 0,
DirectorYN = 0

WHERE
( BMIBatch = @BMIBatch)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return
END

COMMIT TRAN
END

If (@Individual = 'Administrator')
BEGIN
BEGIN TRAN

UPDATE dbo.Batch

SET ApprovedBy = @ApprovedBy,
ApprovedDate = GetDate(),
Status = 2,
RejectReason = @RejectReason

WHERE
( BMIBatch = @BMIBatch)

UPDATE dbo.Que

SET Status = 2,
Route = 'Adjuster',
TeamCoordinatorYN = 0,
AssociateDirectorYN = 0,
DirectorYN = 0

WHERE
( BMIBatch = @BMIBatch)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return
END

COMMIT TRAN
END

Select @ResultMessage = Convert(Varchar(20), @@RowCount) + 'Records Affected'
GO

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-12 : 17:15:47

You can start with formatting the code (nice online tool here http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm)

I haven't looked too closely but it seems like you execute this same block in each conditional


      BEGIN TRAN 
      UPDATE dbo.batch 
      SET    approvedby = @ApprovedBy, 
             approveddate = Getdate(), 
             status = 2, 
             rejectreason = @RejectReason 
      WHERE  (bmibatch = @BMIBatch) 
      UPDATE dbo.que 
      SET    status = 2, 
             route = 'Adjuster', 
             teamcoordinatoryn = 0, 
             associatedirectoryn = 0, 
             directoryn = 0 
      WHERE  (bmibatch = @BMIBatch) 
      IF @@ERROR <> 0 
        BEGIN 
          ROLLBACK TRAN 
          RETURN 
        END 
      COMMIT TRAN

So put all of that in a stored procedure and then inside of each condition, you'd just call the sp, something like

exec p_Updates @ApprovedBy, @RejectReason, @BMIBatch

As I said I didn't look too closely, so you may need to add more parameters to the sp, but this should give you the idea.

JTProg
Starting Member

24 Posts

Posted - 2006-09-12 : 18:18:35

I apologize. I had changed some of the update commands. TC's can't reject an item if they've already approved it. It will be at the higher level. Assoc. Director's can't reject an item if they've already approved it. It will be at the higher level. Certain conditions have to be met in order for the updates to execute. I can't create one update for all scenarios, but I was hoping to consolidate the if statements.

Something like this
If ((@ClosureStatus = 'Team Coordinator') AND (@Individual = 'Team Coordinator' AND @Route = 'Team Coordinator')) OR IF ((@ClosureStatus = 'Associate Director' OR @ClosureStatus = 'Director') AND (@Individual = 'Team Coordinator' AND @TC = 0 AND @Route = 'Team Coordinator'))

could consolidate this:

If (@ClosureStatus = 'Team Coordinator') AND (@Individual = 'Team Coordinator' AND @Route = 'Team Coordinator')
BEGIN
BEGIN TRAN

UPDATE dbo.Batch

SET ApprovedBy = @ApprovedBy,
ApprovedDate = GetDate(),
Status = 2,
RejectReason = @RejectReason

WHERE
( BMIBatch = @BMIBatch)

UPDATE dbo.Que

SET Status = 2,
Route = 'Adjuster',
TeamCoordinatorYN = 0,
AssociateDirectorYN = 0,
DirectorYN = 0

WHERE
( BMIBatch = @BMIBatch)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return
END

COMMIT TRAN
END

IF (@ClosureStatus = 'Associate Director' OR @ClosureStatus = 'Director') AND (@Individual = 'Team Coordinator' AND @TC = 0 AND @Route = 'Team Coordinator')
BEGIN
BEGIN TRAN

UPDATE dbo.Batch

SET ApprovedBy = @ApprovedBy,
ApprovedDate = GetDate(),
Status = 2,
RejectReason = @RejectReason

WHERE
( BMIBatch = @BMIBatch)

UPDATE dbo.Que

SET Status = 2,
Route = 'Adjuster',
TeamCoordinatorYN = 0,
AssociateDirectorYN = 0,
DirectorYN = 0

WHERE
( BMIBatch = @BMIBatch)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return
END

COMMIT TRAN
END

I'm just thinking there is a cleaner method.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-12 : 19:33:28

I'm not sure I understand the problem, but if you are asking if you can do this

quote:
If ((@ClosureStatus = 'Team Coordinator') AND (@Individual = 'Team Coordinator' AND @Route = 'Team Coordinator')) OR IF ((@ClosureStatus = 'Associate Director' OR @ClosureStatus = 'Director') AND (@Individual = 'Team Coordinator' AND @TC = 0 AND @Route = 'Team Coordinator'))

Then yes, just write it as a big compound expression:

IF ((@ClosureStatus = 'Team Coordinator') AND (@Individual = 'Team Coordinator' AND @Route = 'Team Coordinator')) OR ((@ClosureStatus = 'Associate Director' OR @ClosureStatus = 'Director') AND (@Individual = 'Team Coordinator' AND @TC = 0 AND @Route = 'Team Coordinator'))
BEGIN
....
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources