Skip to main content
A
ACS LLC
Inspiring
September 20, 2011
Question

Creating record to accept counter update

  • September 20, 2011
  • 1 reply
  • 1651 views

dbase: MS SQL 2005

I want to update a counter using SQL:

UPDATE mycountertable

SET mycounter = mycounter + 1

WHERE thecounter = 1234

If 'thecounter' with the value of 1234 exists in the dbase, then mycounter will update mycounter by 1, however if the record mycounter =1234 does not exist no update will take place

The question I have is do I have to check if thecounter=1234 exists first, then if not create it with an insert, or is anybody aware of a way for SQL to automatically create the record (in this case it would start with value 1) if the record does not exist, in one swoop, without having to go see if it exists first?

Thanks

Mark

This topic has been closed for replies.

1 reply

S
Steve Sommers
Legend
September 21, 2011

I would create a stored procedure:

CREATE PROCEDURE [incrementTheCounter]

  @id int

AS BEGIN

  declare @counter int

  select @counter = [myCounter] from [myCounterTable] (updlock)

  where [theCounter] = @id

  if @counter is NULL begin

    set @counter = 1

    insert into [myCounter] ( [myCounter], [theCounter] )

    values ( @counter, @id )

  end else begin

    set @counter = @counter + 1

    update [myCounter]

    set [myCounter] = @counter

    where [theCounter] = @id

  end

  select @counter as [theCounter]

  return(0)

END

Then you simply execute the procedure:

exec [incrementTheCounter] @id=1234

and this will return a result set with the incremented counter value. If you don't have the permissions to create the stored procedure, you can break apart the above logic into individual cfquery statements. Just make sure you wrap it all within a cftransaction tag.

A
ACS LLCAuthor
Inspiring
September 21, 2011

wow. I was hoping it might be a little easier than that. Do you think that it would be a lot faster to process that way, than just check if the record exists with a query, then based on that initial check either do an insert query, or an update?

S
Steve Sommers
Legend
September 21, 2011

It depends. If you are incrementing counters a lot, a stored procedure is the most efficient because your sending a single query to the SQL server and recieving back a single result set. If this seldom called, or not called enough to make a significant impact on bandwidth, then doing the logic as two cfquery calls wrapped by a cftransaction may be easier to maintain (stored procedures can be a can of worms to maintain).

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices