-1

Okay so I am accepting payments on my site (via Authorize.Net). The payment form redirects to a receipt page.

I will have a column in the database for an invoice code (column InvoiceCode), which is RRC0A in this instance. Then I will have another column for an 8 digit number (column InvoiceNumber). Then I will have InvoiceCode + InvoiceNumber = InvoiceId. For example, the InvoiceId will be RRC0A + 8 numbers. It will increment as such: 00000000, 00000001, 00000002, etc. Therefore the InvoiceId will be RRC0A00000001. I cannot simply increment the column in my database because there will be other InvoiceCodes that also start at 00000000.

  1. I need to increment the InvoiceNumber by one when I add a new row. How can I grab the last InvoiceNumber that was entered into the database? It must be associated with the InvoiceCode RRC0A. This could occur when more than 1 person is making a payment, so I am not sure of the best way.

  2. How can I pad the incrementing InvoiceNumber with 0's in front so that it is always 8 digits?

RockOn
  • 197
  • 1
  • 19
  • I'd suggest an identity field and a computed field. Do not try to select the last inserted row as you will be wrong. – Matthew Whited Mar 29 '16 at 18:37
  • 1
    Usually is very bad Idea handle consecutive id, is better create one autoincrement field on your db – Juan Carlos Oropeza Mar 29 '16 at 18:37
  • 3
    Don't even pretend that managing incrementing numbers by yourself is a good idea. This is what the identity property is designed to handle. Another option would be a sequence. Both of these handle things like concurrency and rolled back transactions that doing this yourself in incredibly hard to get right and incredibly easy to get very very wrong. – Sean Lange Mar 29 '16 at 18:40
  • Thanks, I hadn't heard of a sequence before. I agree that auto-increment would be better, however, it will not work in this case where I have many InvoiceCodes to deal with. I would have to fill the table with a bunch of auto-increment columns if I did that. – RockOn Mar 29 '16 at 18:48
  • Make sure to throw a unique index on the InvoiceCode + InvoiceNumber – UnhandledExcepSean Mar 29 '16 at 19:48

4 Answers4

2

Using an identity and a computed column you can created you invoice numbers with the correct formatting at the time of insert.

CREATE TABLE [dbo].[Invoices](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Code] [nchar](5) NOT NULL,
    [InvoiceNumber]  AS ([Code]+right('00000000'+CONVERT([nvarchar](10),[ID]),(8))) PERSISTED,
    [Cost] [decimal](18, 2) NOT NULL,
    CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
)

sample bulk insert

INSERT INTO [dbo].[Invoices] ([Code], [Cost])
OUTPUT INSERTED.*
SELECT 'ABC01', 500 UNION ALL
SELECT 'ABC01', 501 UNION ALL
SELECT 'EFG23', 502 UNION ALL
SELECT 'RRAc1', 503 UNION ALL
SELECT 'ABC01', 504

output 

ID  Code    InvoiceNumber   Cost
1   ABC01   ABC0100000001   500.00
2   ABC01   ABC0100000002   501.00
3   EFG23   EFG2300000003   502.00
4   RRAc1   RRAc100000004   503.00
5   ABC01   ABC0100000005   504.00

When you insert your records you can get the ID and InvoiceNumber back at the same time.

The values are also persisted so they may be indexed as you would other columns.

Matthew Whited
  • 22,160
  • 4
  • 52
  • 69
  • 1
    "I cannot simply increment the column in my database because there will be other InvoiceCodes that also start at 00000000". My understanding is the OP wants invoice numbers to start at 0 for each InvoiceCodes. – paparazzo Mar 29 '16 at 19:09
  • Yes that is correct. If I use this code, I would have to create a table for every InvoiceCode. There are dozens of InvoiceCodes right now, and it will only expand in time. – RockOn Mar 29 '16 at 19:11
  • You can do that with a row over in a group update but I would not recomend it. You would be much better off just replacing the prefix with the required code but using the invoice numbers incerment with the identity. You will run into more problems later with keeping values in sync if you try to externalize the update. (You can easily move the prefix to another column in the table and use that as part of the computed value. If you do something like a joint to get that code then you will not be able to use the persisted value.) – Matthew Whited Mar 29 '16 at 19:19
1
    SELECT InvoiceCode, MAX(InvoiceID)
    FROM yourTable t
    GROUP BY InvoiceCode

This should return the latest InvoiceID for each InvoiceCode, but you can add your own WHERE clause to filter it down

As for how to pad-left in sql, check out this answer.

Community
  • 1
  • 1
Wizwert
  • 11
  • 3
  • Thanks this could work. I am afraid though about what happens if two people are filling out a payment form at the same time? – RockOn Mar 29 '16 at 18:49
  • The SQL query itself will be safe in the that situation, the table will have a read-lock on it while the select is running that will prevent an update. You will still need to deal with the issue of out of sync data farther up stream through. – Wizwert Mar 29 '16 at 18:56
  • That is just a lot of unnecessary read locks and MAX(InvoiceID) given you know the InvoiceCode – paparazzo Mar 29 '16 at 19:05
1

A as in one column is just a bad design

Have composite PK
InvCode (varchar), InvInt (int) 

declare @InvCode varchar(20) = 'RRC0A'
 insert into invoice (InvCode, InvInt) 
 OUTPUT INSERTED.InvInt, INSERTED.InvCode
 select @InvCode, isnull(max(InvInt),-1) + 1 
   from invoice  
  where InvCode = @InvCode;

The isnull will deal with the first one

A single statement is a transaction so I don't think two simultaneous could clobber
Even if they did the PK would be violated so the insert would fail

use a view or a computed column for the formatted invoice number

CREATE TABLE [dbo].[Invoice](
    [InvCode] [varchar](10) NOT NULL,
    [InvInt] [int] NOT NULL,
    [Formatted]  AS ([InvCode]+right('00000000'+CONVERT([nvarchar](10),[InvInt]),(8))),
CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED 
(
    [InvCode] ASC,
    [InvInt] ASC
)
paparazzo
  • 44,497
  • 23
  • 105
  • 176
0

  1. You can grab the last InvoiceNumber with a SELECT query.

  2. You can pad the invoice number with the + sign to concatenate two strings, and then use RIGHT() to get the right-most 8 characters.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Don't do this unless you do a where using Scope_Identity() and your primary key. – Matthew Whited Mar 29 '16 at 18:39
  • Scope_identity would only be useful if the SELECT occurred in the same query-context as the most recent INSERT. – Tab Alleman Mar 29 '16 at 18:40
  • You arent going to be ensured to get the correct invoice if you don't get get the idea from the insert. If you just select the last row is it possible that another invoice was inserted before your request. – Matthew Whited Mar 29 '16 at 18:43
  • Well, the way I'm reading the original question, if another invoice gets inserted, then that's the one the OP wants in his results. In his words: "How can I grab the last InvoiceNumber that was entered into the database?" I understand what you're getting at, but I think you're mis-reading the question. – Tab Alleman Mar 29 '16 at 18:46
  • Many people ask questions based on bad assumptions. It's better to help them fix bugs before they find them later. – Matthew Whited Mar 29 '16 at 18:48
  • Let me put it this way: If a user INSERTs an invoice. Then closes SSMS, shuts off his computer, and goes home. The next day when they want to pull up the row they inserted the day before, scope_Identity is not going to help them. And that is what I think the OP requires. – Tab Alleman Mar 29 '16 at 18:53
  • I don't disagree but that is still a bad design. – Matthew Whited Mar 29 '16 at 18:54