Code inside a SQL database

Hi

I work with a VB.net Excel Add-in that is used to upload data to an accounting Transactions table in a SQL database.
A batch ID and Transaction ID is generated in my VB.net code but I would prefer that it is done in the SQL database
itself. I have never coded inside a SQL database. Where would I begin if this is possible.
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
ste5anConnect With a Mentor Senior DeveloperCommented:
It depends one your data and the data model. But with some assumptions:

Create a batch table.

CREATE TABLE dbo.Batches
( 
    BatchID INT IDENTITY NOT NULL,
    BatchDescriptionText NVARCHAR(255) NOT NULL,  
    BatchDateTime DATETIME NOT NULL,
    BatchOwner SYSNAME NOT NULL,
    CONSTRAINT PK_Batchs PRIMARY KEY ( BatchID )
);

ALTER TABLE dbo.Batchs
ADD CONSTRAINT DF_Batchs_BatchDateTime DEFAULT GETDATE() FOR BatchDateTime;

ALTER TABLE dbo.Batchs
ADD CONSTRAINT DF_Batchs_BatchOwner DEFAULT SUSER_SNAME() FOR BatchOwner;

Open in new window

then INSERT your batch descriptor using the OUTPUT clause:

INSERT INTO dbo.Batches ( BatchDescriptionText )
OUTPUT INSERTED.BatchID
VALUES ( 'yourDescrption');

Open in new window


Then you have in the return value the batch ID. You can also wrap this up into a stored procedure.

btw, I don't see the need for batch ID and transaction ID. Otherwise you need also a transaction table following the same scheme.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.