Hi, I have a problem with my IDENTI

[raydenl]

Hi, I have a problem with my IDENTITY column (item_id) and my INSERT statement into my 'inventory' table.

The stored procedure is executed from VB6, if the user enters an error, and the INSERT statement does not complete the IDENTITY column still increments. i.e. The 'amount' field is NOT NULL, if the user forgets to enter an amount, an error occurs in VB and the row is not inserted, but the IDENTITY column still increments.

I am NOT worried about filling in IDENTITY column gaps if a record is deleted, but I do want IDENTITY values to be in sequence (NO GAPS) when inserting records.

I have looked at DBCC CHECKIDENT, but dont understand how to use the values returned from it.

Here is the SP I am using:


CREATE PROCEDURE insert_inventory
@item_id int,
@item_name varchar(20),
@description varchar(100),
@notes varchar(255),
@amount char(8)
AS
SET NOCOUNT ON
DECLARE @transaction_date datetime

BEGIN TRANSACTION

IF (@item_name = '') SET @item_name = NULL
IF (@description = '') SET @description = NULL
IF (@notes = '') SET @notes = NULL
IF (@amount = '') SET @amount = NULL

SET @transaction_date = GETDATE()
INSERT INTO inventory (item_name, item_description, notes) VALUES (@item_name, @description, @notes)
IF @@ROWCOUNT = 0 OR @@ERROR <> 0
BEGIN
RAISERROR('insert_inventory SP FAILED', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
SET @item_id = @@IDENTITY
INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@item_id, @transaction_date, CAST(@amount AS money))
IF @@ROWCOUNT = 0 OR @@ERROR != 0
BEGIN
RAISERROR('insert_inventory SP FAILED', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION

 

[LogicalmanUS]

raydenl,

You have identified your own problem, and your code actaully creates it!

As you say, the table column 'amount' is set as NOT NULL, and therefore any attempt to enter a NULL value will cause a constraint error.
But in your code you have:
IF (@item_name = '') SET @item_name = NULL
IF (@description = '') SET @description = NULL
IF (@notes = '') SET @notes = NULL
IF (@amount = '') SET @amount = NULL
which sets 'amount' to NULL if it's blank, and then it attempts to create a record.

The Procedure should be re-written with a flag variable. That being, if any of the NOT NULL constraints are about to be violated, then the procedure doesn't attempt to do an insert.

Insert the following code:

DECLARE @myFLAG int
SET @myFLAG = 0

before 'BEGIN TRANSACTION'.

Replace the line:
IF (@amount = '') SET @amount = NULL
with
IF (@amount = '') SET @myFLAG = 1
(This line should be repeated for ALL columns having the NOT NULL constraint set)

Insert:
If @myFLAG = 1
BEGIN
Goto No_Save
END

in front of : SET @transaction_date = GETDATE()

Insert:

No_Save:

at the end of the SProc (Remember to include the full colon at the end of that line, then it becomes a label.

Basicly, the inserts above will test for any errors in the data, and if found, sets the flag. Prior to attempting an insert that will obviously fail, the procedure chewcks the flag, and if set, then immediately ignores the rest of the code and exits.
Following the No_Save line you can place any code you like to clean up the SProc.

Hope this assists,

Logicalman