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