Wrox Blog - Stored Procedure Update - Count Comments

Old Pedant · February 11th, 2009, 08:41 PM

I'm going to try ONE MORE TIME to get this forum to accept the formatting I want to use!!!

Oh, sorry! I misread your code, somewhat. I thought you were going to always use your Get_CommentCount to get the counts, one blog entry at a time.

I see now that you just have made the mistake of not trusting SQL Server to do the right thing.

You can GET RID of your Get_CommentCount funtion, entirely, and rewrite the other function thus:

Code:

Public Function Increment_CommentCount(ByVal blogEntryId As Integer) As Integer
    Return CINT( SqlHelper.ExecuteScalar( _
                     _constring, _
                     CommandType.Text, _
                     "Update BlogEntry set commentcount=commentcount+1 where id= @blogEntryId; " & _
                     "SELECT commentcount WHERE id=@blogEntryId;", _
                     New SqlParameter("@blogEntryId", blogEntryId), _
                     New SqlParameter("@blogEntryId", blogEntryId) _
                ) )
End Function

But better, of course, would be to create a simple SP to do that:

Code:

Create Procedure IncrementCommentCount( @id INT )
AS
    SET NOCOUNT ON -- probably don't need this...won't hurt
    UPDATE BlogEntry set commentcount=commentcount+1 where id = @id
    SET NOCOUNT OFF
    SELECT commentcount WHERE id=@id

And then your VB code is reduced to as simple as perhaps:

Code:

Public Function Increment_CommentCount(ByVal blogEntryId As Integer) As Integer
    Return CINT( SqlHelper.ExecuteScalar( _
                     _constring, _
                     CommandType.Text, _
                     "EXECUTE IncrementCommentCount " & blogEntryId _
                ) )
End Function

All untested and I'm writing in this little tiny textarea window, so forgive typos. But hopefully you get the idea.

And I *STILL* think you'd be better off incorporating the increment into a SP where you INSERT the comment into the comments table.

norman001 · February 11th, 2009, 11:22 PM

Thanks! It never occurred to me to do this way..simple and elegant!!
Just added the following statement at the end of the existing CommentInsert procedure..

UPDATE BlogEntry SET CommentCount = CommentCount+1 WHERE id=@blogEntryId

This is not exactly a trigger but in the same procedure with 2 SQL statements..

Old Pedant · February 12th, 2009, 01:08 AM

That's better than a trigger, in the eyes of most DB designers.

You use triggers to handle inserts and updates and deletes that might occur from one of MANY sources.

When you only have on point of insert (the SP whose fragment you showed there), a trigger is overkill.

There are enough problems associated with triggers that they are best left as a kind of "last resort."

Very glad you opted to do it this way. I don't think you'll regret it.