UPDATE Problem... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UPDATE Problem...

Author

Topic

AProgrammer
Starting Member

9 Posts

Posted - 2008-06-04 : 06:43:02

hello,

I have a table called Invoice_Number(Year, Number)

I want to generate a new invoice number always when I want to create a new invoice. I am selecting it using a standard select statement as the following...

SELECT
SI.Customer,
SI.DateInvoice,
dbo.getInvoiceNumber() as InvoiceNumber,
SI.TotalAmount,
SI.Amount as Invoice_Amount,
'N' as Processed
FROM qryDSales_Invoice as SI
INNER JOIN Customers C
ON C.CID = SI.SID

It results the following

ABC 2008-05-04 2080001 23966 13456

ABD 2008-05-04 2080001 2456 125964

LMJ 2008-05-04 2080001 24596 125969

It is always returning the same invoice number as I cannot use an UPDATE query inside the function which can update the Invoice Number in the table (Invoice_Number). How can I do this??

CREATE FUNCTION [dbo].[getInvoiceNumber]
(
)
RETURNS varchar(7)
AS
BEGIN
DECLARE @val varchar(7)
DECLARE @dt DATETIME
SELECT @dt = val FROM getMyDate
SELECT @val =
CASE
WHEN Year(@dt)%2000 < 9 THEN
'20' + CAST(Year(@dt)%2000 as char(1)) + Number
ELSE
'2' + CAST(Year(@dt)%2000 as char(2)) + Number
END
FROM invoice_Number
RETURN @val

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 06:52:52

Are you trying to generate a unique value for invoice Number each time?

AProgrammer
Starting Member

9 Posts

Posted - 2008-06-04 : 07:02:23

Of course,,, but it should be added to the previous invoice number (stored in the table)... For example,

if the Number is 0001, the next one should be 002 and so on... But it should be unique for every record that I select..

And last but not least... that new value (the value upto which the invoice number has been generated after selecting) should be stored in the table Invoice_Number to get the increment the invoice number next time...

So, next time... the invoice number should start from 0006, if 5 invoices were generated...

I hope my explanation is clear...

Thanks...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-04 : 07:08:41

Why don't you make Number column as identity in Invoice_Number table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 07:13:52

quote:
Originally posted by AProgrammer

Of course,,, but it should be added to the previous invoice number (stored in the table)... For example,

if the Number is 0001, the next one should be 002 and so on... But it should be unique for every record that I select..

And last but not least... that new value (the value upto which the invoice number has been generated after selecting) should be stored in the table Invoice_Number to get the increment the invoice number next time...

So, next time... the invoice number should start from 0006, if 5 invoices were generated...

I hope my explanation is clear...

Thanks...

thats exactly what identity column does for you. Make invoice number column as identity as suggested by Harsh

AProgrammer
Starting Member

9 Posts

Posted - 2008-06-04 : 07:15:17

I think it will not completely solve the problem... I want to select each time a new value...

Moreover, at times I have to change the Invoice Number manually (decrement in case of error) which is not possible if I make it an identity column.

What should I do?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 07:26:19

quote:
Originally posted by AProgrammer

I think it will not completely solve the problem... I want to select each time a new value...

Moreover, at times I have to change the Invoice Number manually (decrement in case of error) which is not possible if I make it an identity column.

What should I do?

Why do you select when it generates itself a new value each time?
Error? of what kind?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-06-04 : 08:36:36

What business need must be met by there being no gaps in the sequence number?

AProgrammer
Starting Member

9 Posts

Posted - 2008-06-04 : 08:53:06

Thanks to you all for your comments...

I have solved the problem,,, by using a TEMP Table added to it an Identity Column.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources