SP Confusion

[raydenl]

Arghhhhhhh. Ok I am confused. My SP has two unique random ID generators to produce ID's for the two INSERT statements within it. But now for some reason it wont cause an error if the NOT NULL constraints are violated. It will insert perfectly if all the values are entered, but if I dont enter a value it does nothing (no error). But if I run it from MSSQL QA it does cause an error, but just NOT from VB6 anymore, unless I remove the two number ID generators. Anyone have any idea?


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

BEGIN TRANSACTION

DECLARE @transaction_id char(8)
DECLARE @transaction_date datetime

SET @item_id = 'ITM' + CAST(((99999 - 10000) * Rand() + 10000) AS char)
SELECT * FROM inventory WHERE item_id = @item_id
WHILE @@ROWCOUNT <> 0
BEGIN
SET @item_id = 'ITM' + CAST(((99999 - 10000) * Rand() + 10000) AS char)
SELECT * FROM inventory WHERE item_id = @item_id
END

SET @transaction_id = 'TRN' + CAST(((99999 - 10000) * Rand() + 10000) AS char)
SELECT * FROM expenditure WHERE transaction_id = @transaction_id
WHILE @@ROWCOUNT <> 0
BEGIN
SET @transaction_id = 'TRN' + CAST(((99999 - 10000) * Rand() + 10000) AS char)
SELECT * FROM expenditure WHERE transaction_id = @transaction_id
END

INSERT INTO inventory
VALUES (@item_id, NULLIF(@item_name, ''), NULLIF(@description, ''), NULLIF(@notes, ''))
IF @@ROWCOUNT = 0 OR @@ERROR <> 0
BEGIN
RAISERROR('insert_inventory SP FAILED', 16, 1)
ROLLBACK TRANSACTION
RETURN
END

SET @transaction_date = GETDATE()
INSERT INTO expenditure (transaction_id, item_id, transaction_date, amount)
VALUES (@transaction_id, @item_id, @transaction_date, CAST(NULLIF(@amount, '') AS money))
IF @@ROWCOUNT = 0 OR @@ERROR != 0
BEGIN
RAISERROR('insert_inventory SP FAILED', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION