I am writing an update statement where I need to update a list of items all at once with incremental dates. The incremental value is user defined. I found an example that is pretty similar to my needs at <http://haacked.com/archive/2004/02/28/sql-auto-increment.aspx> , but do not know how I would implement it. That example is:

```
DECLARE @counter int
SET @counter = 0
UPDATE #tmp_Users
SET @counter = counter = @counter + 1

```

My current statement is:

```
strSQL.CommandText = "Update tblItem set item_timed_close=convert(datetime, @item_timed_close),item_timed_start=convert(money, case when item_est_lo < 500 then ((convert(int,item_est_lo+25)/50)*50) when item_est_lo < 1000 then ((convert(int,item_est_lo+50)/100)*100) when item_est_lo < 3000 then ((convert(int,item_est_lo+125)/250)*250) when item_est_lo < 5000 then ((convert(int,item_est_lo+250)/500)*500) else ((convert(int,item_est_lo+12.5)/25)*25) end ) Where item_sale_id=@item_sale_id";
  strSQL.Parameters.Add(new SqlParameter("@item_timed_close", SqlDbType.VarChar, 100, ParameterDirection.Input, true, 0, 0, "item_timed_close", DataRowVersion.Current, datetime_Var.AddMinutes(minutes_Var += Increments_var)));
  strSQL.Parameters.Add(new SqlParameter("@item_sale_id", SqlDbType.VarChar, 100, ParameterDirection.Input, true, 0, 0, "item_sale_id", DataRowVersion.Current, itemSaleId3_Var));

```

datetime\_Var is user defined, and is a `DateTime` format. minutes\_Var equals zero to start with. Increments\_var is user defined. I need `item_timed_close` to increment by whatever the `Increments_var` is set to.

**EDIT**

Results I am looking for would be something like this:

User specifies a starting date of `2012-01-08 12:00:00 PM` and an increment of `10` seconds. For each row updated, the date would look like:

```
2012-01-08 12:00:00 PM
2012-01-08 12:00:10 PM
2012-01-08 12:00:20 PM
2012-01-08 12:00:30 PM
2012-01-08 12:00:40 PM
2012-01-08 12:00:50 PM
2012-01-08 12:01:00 PM
2012-01-08 12:01:10 PM
2012-01-08 12:01:20 PM

```

The first date could even have the incremented value already added to it for all we care, so long as it increments. I could change the initial starting date with that in mind through code.

**Update**

With Naval's suggestion, I tried a sub query. First, I set all date fields to NULL. Then, I run the following query:

```
        strSQL3.CommandText = "Update tblItem set item_timed_close=DATEADD(minute,((Select count(*) as Count From tblItem Where item_sale_id=@item_sale_id And item_timed_close Is NULL) * @increment),convert(datetime, @item_timed_close)),item_timed_start=convert(money, case when item_est_lo < 500 then ((convert(int,item_est_lo+25)/50)*50) when item_est_lo < 1000 then ((convert(int,item_est_lo+50)/100)*100) when item_est_lo < 3000 then ((convert(int,item_est_lo+125)/250)*250) when item_est_lo < 5000 then ((convert(int,item_est_lo+250)/500)*500) else ((convert(int,item_est_lo+12.5)/25)*25) end ) Where item_sale_id=@item_sale_id";
        strSQL3.Parameters.Add(new SqlParameter("@item_timed_close", SqlDbType.VarChar, 100, ParameterDirection.Input, true, 0, 0, "item_timed_close", DataRowVersion.Current, datetime_Var));
        strSQL3.Parameters.Add(new SqlParameter("@item_sale_id", SqlDbType.Int, 5, ParameterDirection.Input, true, 0, 0, "item_sale_id", DataRowVersion.Current, itemSaleId3_Var));
        strSQL3.Parameters.Add(new SqlParameter("@increment", SqlDbType.Int, 5, ParameterDirection.Input, true, 0, 0, "increment", DataRowVersion.Current, Increments_var));

```

I was hoping that I could multiply the sub query by the variable passed in, but it didn't work as expected. It seems to multiply the `@increment` by 1 for every field. I've run the select statement alone and it returns 471. So I know that part works correctly. Do I need to cast `Count` as an integer or something?

**DONE**

I finally got it. I should have thought of this initially.... I had to change the counter variable in my update string's regular `Set` sequence. Here is my working query.

```
 Declare @auto Int 
 Set @auto = 0 

 Update tblItem 
    set item_timed_close = DATEADD(minute, (@auto * @increment), convert(datetime, @item_timed_close))
      , item_timed_start = convert(money, 
                case when item_est_lo < 500  then ((convert(int, item_est_lo+25)/50)*50) 
                     when item_est_lo < 1000 then ((convert(int, item_est_lo+50)/100)*100) 
                     when item_est_lo < 3000 then ((convert(int, item_est_lo+125)/250)*250) 
                     when item_est_lo < 5000 then ((convert(int, item_est_lo+250)/500)*500) 
                     else ((convert(int,item_est_lo+12.5)/25)*25) end )
      , @auto = (@auto + 1) 
  Where item_sale_id=@item_sale_id

```

So I used the original example I had found, and just took out the last `Set @counter` part and put that into my update string's `set` parameters.