-
Auto increment
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
-
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
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|