CODEWITHSUNDEEP
sql-serverstored-procedures
AlteredConcept
AlteredConcept

Reputation: 2632

SQL Stored Procedure Comparing values

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

Upvotes: 1

Views: 4319

Answers (4)

edosoft
edosoft

Reputation: 17271

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

Upvotes: 1

Some Canuck
Some Canuck

Reputation: 846

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)

Upvotes: 0

dkretz
dkretz

Reputation: 37655

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.

Upvotes: 0

John Saunders
John Saunders

Reputation: 161783

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)))

Upvotes: 2

Related Questions