Stored Procedure Group Invoice records and update accordingly

[Richard.Randle]

Hi , I am a Newbie using SQL server and vb.net in VS2010.

I am re-writing an exisitng application which is written in an old programming language.

I have a table with invoice records waiting to be assigned an invioce number and invoice date.

I have two ways of uupdating these records , the first way is the user manually selects which records/rows to be updated for a specific customer account number. This all works fine.

I am trying to write a stored proc which goes through all un-invoiced records (i.e invoice number = blank) , it is to group records/rows by account number and assign next invoice number (which i already have astroed proc to fetch) and then when the account number changes to move onto the next batch of records for the next account.

As i said earlier i have this already in another language , i am just trying to get my head around it in an SQL stored Proc.

Any help would be most appreciated

Regards Richard

[alc6379]

I can outline the steps that you'll want to take in a sproc, and if you have questions about each step, try Googling the tasks I outline :)

Step 1: Write a query that returns the rows where invoice number is null or blank. Use an ORDER BY statement to group them by their account number.

Step 2:
Use the results of this query in a CURSOR. This cursor should have logic in it to pull the next invoice number. If you have a sproc to do that, great!

Step 3: Close the CURSOR.

It should be that simple if you use a cursor. You should look up how those function:

http://www.mssqltips.com/tip.asp?tip=1599

[Richard.Randle]

Thanks ALC , I had already googled it aswell , and picked upon the cursors thing . And have got a couple of rudementary SProcs written. I ahve three different scenarios though , depending on how the customer record is setup (a choice list) the invoices can be released in different ways , i.e 1) put all un-invoiced records on one invoice number for each account, 2) put all records with account AND Delivery note number on one invoice number for each account(i.e grouping Delnote numbers after account group) 3) one record one invoice number . So now I am trying to get all three different scenarios in one sProc .
So in a nutshell, for each Account I check for how to number the invoices (either 1) , 2) , 3) as above) and update that accounts records and then move to the next acocunt and so on .

Any tips would be greatly appreciated , I have done option 1 and 2 as seperate sProcs but i need to do 3 and try to get them all into one sProc.

Again thanks for your pointers earlier , the ORDER_BY was sometihng I had overlooked but will be a big help.

Richard