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

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.

From stackoverflow

• 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
  

• 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);
  

• 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]