[RESOLVED] SQL Server 2005: Update record if changed

Hi,

I'm trying to write a stored procedure that updates a record only if the values have changed. This is what I have so far.

If any of the numeric fields change e.g. the sort order changes it does not update because the IsNumeric calls evaluate to true.

If I remove the IsNumeric call and the numeric fields are null, it returns that an update is required when it isn't.

Does anyone have any ideas on how to make this work?

Code:

---

IF NOT EXISTS(
                SELECT NULL
                FROM myTable
                WHERE Id=@Id
                AND IsNull(JCode,'') COLLATE Latin1_General_CS_AS=IsNull(@Code,'')
                AND IsNull(ACode,'') COLLATE Latin1_General_CS_AS=IsNull(@ACode,'')
                AND IsNull(PCode,'') COLLATE Latin1_General_CS_AS=IsNull(@PCode,'')
                AND Revised=@Rev -- bit
                AND IsNumeric(Review_Time)=IsNumeric(@RevTime) -- could be null
                AND IsNumeric(Sort_Order)=IsNumeric(@SortOrder)
        )

        BEGIN
        Print 'Do update'
        END
Else
        BEGIN
        Print 'No update required this record exactly matches what we have already'
        END

---

Note: "COLLATE Latin1_General_CS_AS" is being used to force case sensitivity

Additionally would an Upper or Lower call be better/faster than using collate?

Cheers Al

I believe I've solved this, I was wrongly under the impression that you could only use the isNull function for character (string) datatypes.

Just need to provide the IsNull replacement value in the expected data type as follows. I'm using -1, which works for me.

Code:

---

IF NOT EXISTS(
                SELECT NULL
                FROM myTable
                WHERE Id=@Id
                AND IsNull(JCode,'') COLLATE Latin1_General_CS_AS=IsNull(@Code,'')
                AND IsNull(ACode,'') COLLATE Latin1_General_CS_AS=IsNull(@ACode,'')
                AND IsNull(PCode,'') COLLATE Latin1_General_CS_AS=IsNull(@PCode,'')
                AND IsNull(Revised,-1)=IsNull(@Rev,-1) -- bit
                AND IsNull(Review_Time,-1)=IsNull(@RevTime,-1) -- numeric could be null
                AND IsNull(Sort_Order,-1)=IsNull(@SortOrder,-1) -- numeric
        )

        BEGIN
        Print 'Do update'
        END
ELSE
        BEGIN
        Print 'No update required this record exactly matches what we have already'
        END

---