Results 1 to 2 of 2

Thread: SP confusion

  1. #1
    Join Date
    Sep 2003
    Posts
    34

    Unhappy Unanswered: SP confusion

    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

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Is the NULLIF part working correctly?

    Also, have you manually inserted a value of NULL into a column witha constraint on it to see if it errors?

    Cheers

    SG

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •