SQL Stored Procedure Comparing values

Question

Hi all,

Suppose i have the following table

book
-----
id
name
genre
check_out_date
location_id

Can anyone post an example of an efficient stored procedure that only updates the book table where the passed in values are different from the current values in the table?

i.e. if i passed in values ( 1, "Slaughterhouse V", "Fiction", 10/10/2008, 54) and the values in the table are (1, "Slaughterhouse V, "Fiction", 10/24/2009, 70), it would only update the check_out_date and the location_id columns.

Update Book set check_out_date=@check_out_date (10/24/2009), location_id=@location_id (70).

Thanks in advance

Answer 1

UPDATE book
SET name = @name,
    genre = @genre,
    check_out_date = @check_out_date,
    location_id = @location_id
FROM BOOK
WHERE (id = @id) AND
     (((@name IS NULL AND name IS NOT NULL) OR
       (@name IS NOT NULL AND name IS NULL) OR
       (@name IS NOT NULL AND name IS NOT NULL AND name <> @name)) OR 
      ((@genre IS NULL AND genre IS NOT NULL) OR
       (@genre IS NOT NULL AND genre IS NULL) OR
       (@genre IS NOT NULL AND genre IS NOT NULL AND genre <> @genre)) OR 
     ((@check_out_date IS NULL AND check_out_date IS NOT NULL) OR
       (@check_out_date IS NOT NULL AND check_out_date IS NULL) OR
       (@check_out_date IS NOT NULL AND check_out_date IS NOT NULL AND
        check_out_date <> @check_out_date)) OR 
     ((@location_id IS NULL AND location_id IS NOT NULL) OR
       (@location_id IS NOT NULL AND location_id IS NULL) OR
       (@location_id IS NOT NULL AND location_id IS NOT NULL AND
        location_id <> @location_id)))

Answer 2

The most efficient option would be to make your comparisons in the BL or possibly DAL and make the update-or-not decision there.

You're probably reading the record anyway if you know it's an update not an insert. And there's probably other validation too. Handle it all in one place.

Answer 3

You might not want to do this, but you could also first DELETE the record with the matching ID and then INSERT it again.

Answer 4

You said a stored procedure, so no reason not to keep it simple and do separate updates.

UPDATE book SET name = @name WHERE id = @id and name <> coalesce(@name,name)
UPDATE book SET genre = @genre WHERE id = @id and genre <> coalesce(@genre,genre)
UPDATE book SET check_out_date = @check_out_date WHERE id = @id and check_out_date <> coalesce(@check_out_date,check_out_date)
UPDATE book SET location_id WHERE id = @id and location_id <> coalesce(@location_id,location_id)