Comparing values in SQL

I have a sql server 2008 table with two nullable decimal values and an ID. I am passing the values and an ID into a stored procedure. These values m开发者_如何转开发ay be the same as the existing values, or they may be something else. If it is something else, I want to update the existing record. My question is, I'm not sure how to do this because of the nullable factor.

DECLARE @count int
SET @count=(SELECT Count(ID) FROM [MyTable] WHERE 
  [ID]=@id AND 
  [Value1]<>@value1 AND
  [Value2]<>@value2
)

IF (@count>0)
BEGIN
  UPDATE
    [MyTable]
  SET
    [Value1]=@value1,
    [Value2]=@value2
  WHERE
    [ID]=@id
END

My problem is handling the null case. Can somebody please show me how to handle the null case that I described?

Thank you!

Use ISNULL to provide a default value for Value1 and Value2 in your comparison. And I think you'll want an OR if you you want to update if either value is different.

SELECT @count = Count(ID) FROM @MyTable 
WHERE ID = @id 
   AND (ISNULL(Value1,0) <> @value1 OR ISNULL(Value2,0) <> @value2)

You should use IsNull. If you want to nulls to be treated equivalently use something like

IsNull(Value1,'') <> IsNull(@value1,'')

Do you mean if the values passed in are null you don't want to overwrite the existing value?

If so, you could do

UPDATE
  [MyTable]
SET
  [Value1]=ISNULL(@value1, [Value1]),
  [Value2]=ISNULL(@value2, [Value2])
WHERE
  [ID]=@id

If that is what you're looking to do you might want to amend your select as well so that you don't perform unnecessary updates if values passed in are null.

It is expensive and wasteful to do a count first and then perform the update. You're essentially going to do two scans instead of one if the count comes back > 0. Even using an exists instead of a count can be no less efficient but it could be just as bad. Why not just try to run the update? You need to pick some token value for comparison, so a number that could never exist in the data naturally. Or you could run many conditions in the OR.

CREATE PROCEDURE dbo.MyTable_Update
    @ID INT,
    @Value1 DECIMAL(5,2),
    @Value2 DECIMAL(5,2)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.MyTable
    SET Value1 = @Value1,
        Value2 = @Value2
    WHERE
        ID = @id
        AND 
        (
            COALESCE(Value1, -1) <> COALESCE(@Value1, -1)
            OR COALESCE(Value2, -1) <> COALESCE(@Value2, -1) 
        );

    SELECT 'Rows updated:', @@ROWCOUNT;
END
GO

And here is a version that uses more conditions but avoids the need to pick some token value like -1:

CREATE PROCEDURE dbo.MyTable_Update
    @ID INT,
    @Value1 DECIMAL(5,2),
    @Value2 DECIMAL(5,2)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.MyTable
    SET Value1 = @Value1,
        Value2 = @Value2
    WHERE
        ID = @id
        AND 
        (
          Value1 <> @Value1
          OR (Value1 IS NULL AND @Value1 IS NOT NULL)
          OR (Value1 IS NOT NULL AND @Value1 IS NULL)
          OR Value2 <> @Value2
          OR (Value2 IS NULL AND @Value2 IS NOT NULL)
          OR (Value2 IS NOT NULL AND @Value2 IS NULL)
        );

    SELECT 'Rows updated:', @@ROWCOUNT;
END
GO

The key is, don't get a count first, just to see if you should run the update query. Just run the update query. The worst that will happen is you update 0 rows, but at least you will only do it in one pass.