SqlSelect: Update if exists, Insert if not - With date part comparison?

Question

I need to update a record in a database with the following fields

[ID] int (AutoIncr. PK)
[ScorerID] int
[Score] int
[DateCreated] smalldatetime

If a record exists for todays date (only the date portion should be checked, not the time) and a given scorer, I'd like to update the score value for this guy and this day. If the scorer doesn't have a record for today, I'd like to create a new one.

I'm getting grey hair trying to figure how to put this into a single (is this possible?) sql statement. By the way I'm using an MSSQl database and the ExecuteNonQuery() method to issue the query.

Answer 1

IF EXISTS (SELECT NULL FROM MyTable WHERE ScorerID = @Blah AND CONVERT(VARCHAR, DateCreated, 101) = CONVERT(VARCHAR, GETDATE(), 101))
    UPDATE MyTable SET blah blah blah
ELSE
    INSERT INTO MyTable blah blah blah

Answer 2

CREATE PROCEDURE InsertOrUpdateScorer(@ScorerID INT, @Score INT)
AS
BEGIN
  IF EXISTS (
    SELECT 1 
    FROM Scorer 
    WHERE ScorerID = @ScorerID AND DATEDIFF(dd, GETDATE(), DateCreated) = 0
  )
  BEGIN
    UPDATE
      Scorer
    SET 
      Score = @Score
    WHERE
      ScorerID = @ScorerID

    RETURN @ScorerID
  END
  ELSE
  BEGIN
    INSERT 
      Scorer 
      (ScorerID, Score, DateCreated)
    VALUES
      (@ScorerID, @Score, GETDATE())

    RETURN SCOPE_IDENTITY()
  END
END

Use the return value of the procedure to grab the new ScorerId.

SqlCommand UpdateScorer = New SqlCommand("InsertOrUpdateScorer", DbConn);
UpdateScorer.CommandType = CommandType.StoredProcedure;

SqlParameter RetValue = UpdateScorer.Parameters.Add("RetValue", SqlDbType.Int);
RetValue.Direction = ParameterDirection.ReturnValue;

SqlParameter Score = UpdateScorer.Parameters.Add("@Score", SqlDbType.Int);
Score.Direction = ParameterDirection.Input;

SqlParameter ScorerId = UpdateScorer.Parameters.Add("@ScorerID", SqlDbType.Int);
ScorerId.Direction = ParameterDirection.Input;

Score.Value = 15;    // whatever
ScorerId.Value = 15; // whatever

UpdateScorer.ExecuteNonQuery();
Console.WriteLine(RetValue.Value);

Answer 3

The other guys have covered 2005 (and prior) compatible T-SQL/apprroaches. I just wanted to add that if you are lucky enough to be working with SQL Server 2008, you could take advantage of the new Merge (sometimes referred to as Upsert) statement.

I had trouble finding a blog entry or article which explains it further, but I did find this rather (1) helpful entry. The official MSDN entry is (2) here.

(1) [http://www.sqlservercurry.com/2008/05/sql-server-2008-merge-statement.html]
(2) [http://msdn.microsoft.com/en-us/library/bb510625.aspx]