Concurent Invoice Number with SQL Server Store Procedure

admin Leave a comment

There might have many way to manage running number as invoice or other sequence number generating but I would like to bring two among them.

With SQL Server, I would like to show you about what I have been implemented in my previous application in generating invoice running number.

1. Using table to store last running number, store procedure and transaction

We firstly need to create a running number storage table

Create table tblInvoice with fields
InvoiceNumber (int, not null, primary key)
InvoiceType (char(3)).

Then create store procedure which is used to manage its value

PROCEDURE [dbo].[UDP_GETINVOICENUMBER]
@invoice_type as char(3),
@invoice_number AS BIGINT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
UPDATE tblInvoice
SET @invoice_number=InvoiceNumber=InvoiceNumber+1
WHERE(InvoiceType=@invoice_type)
COMMIT TRANSACTION
END

2. Using Identity field
This mean that we need to use the function SCOPE_IDENTITY() function to get the recent added row.
Read More

Export SQL Server database diagram,

admin Leave a comment

I know that some of you might want to do like me, export database diagram to be executed in other server. This case would meet when we want to have a very blank database but remain all database schema of an existing database one.

Normally we can do a back up then restore it as a new name then run the script to truncate all data in every tables but it will be difficult if it was build with diagram.

I have found on the net the sample on SQL Server 2000 and also 2005. It is a very helpfull for my case as I need to re-create database schemas on my new blank database.

1. SQL 2000

There have 2 store procedures and a function is needed

ufn_VarbinaryToVarcharHex

CREATE FUNCTION dbo.ufn_VarbinaryToVarcharHex (@VarbinaryValue
varbinary(4000))
RETURNS Varchar(8000) AS
BEGIN

Declare @NumberOfBytes Int
Declare @LeftByte Int
Declare @RightByte Int

SET @NumberOfBytes = datalength(@VarbinaryValue)

IF (@NumberOfBytes > 4)
RETURN Payment.dbo.ufn_VarbinaryToVarcharHex(cast(substri ng(@VarbinaryValue,1,
(@NumberOfBytes/2)) as varbinary(2000)))+ Payment.dbo.ufn_VarbinaryToVarcharHex(cast(substri ng(@VarbinaryValue,((@NumberOfBytes/2)+1),2000) as varbinary(2000)))

IF (@NumberOfBytes = 0)
RETURN ''


-- Either 4 or less characters (8 hex digits) were input
SET @LeftByte = CAST(@VarbinaryValue as Int) & 15
SET @LeftByte = CASE WHEN (@LeftByte < 10)
THEN (48 + @LeftByte)
ELSE (87 + @LeftByte)
END
SET @RightByte = (CAST(@VarbinaryValue as Int) / 16) & 15
SET @RightByte = CASE WHEN (@RightByte < 10)
THEN (48 + @RightByte)
ELSE (87 + @RightByte)
END
SET @VarbinaryValue = SUBSTRING(@VarbinaryValue, 1,(@NumberOfBytes-1))

RETURN CASE WHEN (@LeftByte < 10)
THEN
Payment.dbo.ufn_VarbinaryToVarcharHex(@VarbinaryVa lue) + char(@RightByte) + char(@LeftByte)
ELSE
Payment.dbo.ufn_VarbinaryToVarcharHex(@VarbinaryVa lue) + char(@RightByte) + char(@LeftByte)
END


END
go

GRANT EXECUTE ON [dbo].[ufn_VarbinaryToVarcharHex] TO [PUBLIC]
GO

Read More