Auto increment

[knicholson]

I would like to avoid using a cursor. I am updating several rows in a table with sequential numbers starting at a number I pass into the Stored Procedure. Is there a way to do this with one update statement?

Thanks,
Ken Nicholson
Sara Lee Corporation

[cs_troyk]

Ken;

We need more info to suggest a solution. Can you provide simplified DDL (CREATE TABLE statement(s)), some sample data (INSERTs) and expected results (i.e., sample "before" and "after" rows)?


TroyK, MCSD

[knicholson]

The stored procedure is inserting records into a history table and the numbers are check numbers. For example, the table composition is:

Create table history (
HistoryDate datetime,
Account int,
Amount money,
Checknumber int,
Check Status char(1))

Create table customer (
Account int,
AddressUID uniqueidentifier,
CashBalance money)

The stored procedure will be called with the starting check number
CREATE PROCEDURE [Refunds]
(@StartingCheckNumber int)
AS
Insert into History (HistoryDate, Account, Amount, StartingCheckNumber)
Select getdate(), Account, CashBalance, [NextCheckNumber]
From customer
Where CashBalance > 0
GO

The [NextCheckNumber] is where I am having a problem. I tried using a user defined function but could not figure out how to make the number increment.

Any suggestions would be appreciated.

Thanks,
Ken Nicholson
Sara Lee Corporation

[cs_troyk]

Ken;

I think this stored proc should do what you want. This solution assumes that account numbers will be unique. I might add that this design appears to be very "fragile". I would probably have a separate table for assigning the check numbers. Also of concern is what happens when a 'history' entry is made, but the CashBalance remains in the 'customer' table. It will get another 'history' entry the next time the proc is run.

That being said:

CREATE PROCEDURE [Refunds]
(@StartingCheckNumber int)
AS
Insert into History (HistoryDate, Account, Amount, CheckNumber)
Select getdate(),
Account,
CashBalance,
CheckNumber = @StartingCheckNumber +
(SELECT COUNT(*)
FROM customer
WHERE Account < c1.Account
AND CashBalance > 0)
From customer c1
Where c1.CashBalance > 0
GO


HTH,
TroyK, MCSD