CODEWITHSUNDEEP
sqlsql-server
RUPA
RUPA

Reputation: 211

Get only updated columns by using OUTPUT Clause sql

I have following procedure to track column changes in the table.

ALTER PROCEDURE [UpdateAddress]
@id bigint=0,
@fullname varchar(200)='',
@address varchar(200)='',
@city varchar(200)='',
@state varchar(200)=''
AS
declare @desc varchar(500);
BEGIN
DECLARE @MyTableVar table(  
   fullname varchar(200) NULL,  
   address varchar(200) NULL , 
  city varchar(200) NULL ,
 state varchar(200) NULL );


        BEGIN
            UPDATE [Address]
            SET 
                fullname=@fullname,
                address=@address,
                city=@city,
                state=@state

             OUTPUT deleted.fullname,  
      deleted.address,  
      deleted.city,  
      deleted.state
INTO @MyTableVar
             WHERE [Id]=@id

here i will get all columns which are updated and not updated. So we need to compare both like deleted.fullname and inserted.fullname . So, if I have 20 columns in a table, and only 2 columns i updated. without comparing , is there any way to get only updated columns using output clause?

Example: I have 20 columns. in that only fullname and address i updated. so i should get Fullname,Address changed. without any comparison. Is there any sql built-in function like updated_column()?

Upvotes: 0

Views: 828

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You can use scalar expressions in the output. So, you could represent "no change" as, say, NULL:

OUTPUT (case when inserted.fullname <> deleted.fullname then deleted.fullname end),  
       (case when inserted.address <> deleted.address then deleted.address end),
       (case when inserted.city <> deleted.city then deleted.city end),  
       (case when inserted.state <> deleted.state then deleted.state end)

This is unambiguous if the previous column values are never NULL.

You could also use the same idea to list together the columns that are changed:

OUTPUT deleted.fullname,  
       deleted.address,  
       deleted.city,  
       deleted.state,
       ((case when inserted.fullname <> deleted.fullname then 'fullname;' else '' end) + 
        (case when inserted.address <> deleted.address then 'address;' else '' end) + 
        (case when inserted.city <> deleted.city then 'city;' else '' end) +   
        (case when inserted.state <> deleted.state then 'state;' else '' end)
       )

The expressions would be a bit more complicated to include NULL checks.

Upvotes: 2

Related Questions