CODEWITHSUNDEEP
sqlsql-server
BruceyBandit
BruceyBandit

Reputation: 4334

How to keep params as original values when performing an update

I am not sure if this is possible but thought I'll ask anyway.

I have a stored procedure where it will perform an update on a table. This stored procedure has five parameters:

@ClawbackID         int,
@PaymentID          int,
@ClawbackDate       date,
@ClawbackPercent    money,
@ClawbackAmount     money

So when the user executes the procedure, they need to fill in the values for the 5 parameters.

Now lets say for this occasion I want to update only 2 of the parameters, it means I still have to enter in the same values for the 3 parameters I don't want to change and enter in two new values for the 2 parameters I want to change. This could lead to more chances of human error.

I was wondering if there is a way it can be set so that parameters I don't want to change, I just keep the parameter name and the ones I do want t ochange, I enter in their values?

Example below where I change 2 of the 5 parameters is:

exec procexample @ClawbackID, @PaymentID, '2017-03-06', @ClawbackPercent, 1540.00

So the three params keep their original value and the two values entered are changed.

However this will need to be dynamic as on another occasion I may want to update 4 values, then next occasion could be only 1 value etc.

Is this possible?

Thank you

Upvotes: 0

Views: 451

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

I know it's late, but I wanted to clean this one up. (see the comments in the code)

-- Create a Dummy Table/Record to Log Param -- This would be an Actual table
Declare @LogParam Table (Log_ID int IDENTITY(1,1),PrcName varchar(250),ExecDT DateTime ,UsrID int,ParamXML XML);
Insert Into @LogParam Values (OBJECT_NAME(@@PROCID),GetUTCDate(),1,'<ClawbackID>25</ClawbackID><PaymentID>5</PaymentID><ClawbackDate>2016-09-01</ClawbackDate><ClawbackPercent>0.5000</ClawbackPercent><ClawbackAmount>50000.0000</ClawbackAmount>');

-- In Your Stored Proc Collect Parameters (Let's assume only the ClawbackAmout was passed)
Declare @UsrID int = 1,@ClawbackID int,@PaymentID int,@ClawbackDate date,@ClawbackPercent money,@ClawbackAmount money = 25000

-- Collect and Set Values
Select @ClawbackID      = IsNull(@ClawbackID     ,ParamXML.value('(ClawbackID)[1]'     ,'int'))
      ,@PaymentID       = IsNull(@PaymentID      ,ParamXML.value('(PaymentID)[1]'      ,'int'))
      ,@ClawbackDate    = IsNull(@ClawbackDate   ,ParamXML.value('(ClawbackDate)[1]'   ,'date'))
      ,@ClawbackPercent = IsNull(@ClawbackPercent,ParamXML.value('(ClawbackPercent)[1]','money'))
      ,@ClawbackAmount  = IsNull(@ClawbackAmount ,ParamXML.value('(ClawbackAmount)[1]' ,'money'))
 From  @LogParam 
 Where UsrID = @UsrID
   and IsNull(PrcName,'') = IsNull(OBJECT_NAME(@@PROCID),'')
 Order By Log_ID Desc

-- Log Latest Parameters
Insert Into @LogParam values (OBJECT_NAME(@@PROCID),GetUTCDate(),@UsrID,(Select ClawbackID = @ClawbackID,PaymentID = @PaymentID,ClawbackDate = @ClawbackDate,ClawbackPercent = @ClawbackPercent,ClawbackAmount  = @ClawbackAmount  For XML Path('')))


-- Just to Illustrate Current Log and Current Parameters
Select * from @LogParam
Select @ClawbackID
      ,@PaymentID 
      ,@ClawbackDate 
      ,@ClawbackPercent
      ,@ClawbackAmount

Results

enter image description here

The PrcName is NULL, but that would be calling Procedure Name

You may also notice the cost of of the Collection and Save was only 3 ms.

Upvotes: 0

Cee McSharpface
Cee McSharpface

Reputation: 8725

Usually you would pass NULL as the value for those parameters which you do not want to cause a change, and then use logic inside the stored procedure:

UPDATE t SET
    t.ClawbackDate=COALESCE(@ClawbackDate, t.ClawbackDate)
FROM UpdatableTable t
WHERE <criteria>

You can optimize that further when you bypass the UPDATE altogether in case all parameters are NULL, and in the WHERE clause, so records only get updated when there is an actual change:

WHERE t.ClawBackDate!=COALESCE(@ClawbackDate, t.ClawBackDate) OR ...

Upvotes: 1

Related Questions