CODEWITHSUNDEEP
sql-serverstored-procedures
tavier
tavier

Reputation: 1794

SQL stored procedure conditional update for one of the cloumns

I have this update statement in my SP:

ALTER PROCEDURE [dbo].[mySP]
      @ID INT
    , @valA NVARCHAR(500) 
    , @valB NVARCHAR(500)
    , @valC NVARCHAR(500)
    , @flag BIT = 0
UPDATE mt
    SET mt.colA = @valA,
        mt.colB = @valB,
        mt.colC = @valC,
        mt.colD = GETUTCDATE()
    FROM dbo.mytable mt
    WHERE mt.ID = @ID

I want to change it to update colD only if @flag is 1. What would be the best and efficient way without impacting the performance of the SP much?

Upvotes: 0

Views: 45

Answers (2)

Red Devil
Red Devil

Reputation: 2403

Try this:

ALTER PROCEDURE [dbo].[mySP]
      @ID INT
    , @valA NVARCHAR(500) 
    , @valB NVARCHAR(500)
    , @valC NVARCHAR(500)
    , @flag BIT = 0
UPDATE mt
    SET mt.colA = @valA,
        mt.colB = @valB,
        mt.colC = @valC,
        mt.colD  = case when  @flag = 1 then  GETUTCDATE() else  mt.colD END
    FROM dbo.mytable mt
    WHERE mt.ID = @ID

Upvotes: 1

Thom A
Thom A

Reputation: 96015

Would seem the easiest way would be to use a CASE expression:

mt.colD = CASE @Flag WHEN 1 THEN GETUTCDATE() ELSE mt.ColD END

Upvotes: 4

Related Questions