[RESOLVED] SQL Server 2005: Update record if changed

[aconybeare]

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?

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

[aconybeare]

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.

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