Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    34

    Unanswered: Problem with a SP. Help!

    Why does only the first INSERT statement work???


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

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

    SET @item_id = IDENT_CURRENT('inventory')
    SET @transaction_date = GETDATE()

    INSERT INTO inventory (item_name, item_description, notes) VALUES (@item_name, @description, @notes)

    INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@item_id, @transaction_date, CAST(@amount AS money))



    Also, where I have IF(@blah = '') SET....

    I use these to convert blank fields in VB6 to NULL values, is there any easier way???

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    do you get any error on the second insert?

    if @blah='' set... can be removed, but you will have to check for the value somewhere if you care for it: either in your vb6, in the if as you do now, or in the values clause (...values (nullif(@blah, ''), ...)

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Problem with a SP. Help!

    Try to change your SP this way (you have to get id after insert - not before):


    SET @transaction_date = GETDATE()
    begin tran
    INSERT INTO inventory (item_name, item_description, notes) VALUES (@item_name, @description, @notes)
    if @@error<>0 begin
    rollback
    return
    end
    SET @item_id = IDENT_CURRENT('inventory')

    INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@item_id, @transaction_date, CAST(@amount AS money))
    commit

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    i think ident_current(..) returns the _last_ new identity value for the specified table, not the identity value generated by the current scope. in this case if your insert occurred before another insert in another scope you may acquire the value that is not yours. scope_identity() guarantees that identity value belongs to insert from your session.

    also, it's better to write the error handler this way:

    declare @error int, @id int
    ...insert operation
    select @error = @@error, @id = scope_identity()
    if @error <> 0 begin
    raiserror (...)
    rollback tran
    return (1)
    end
    commit tran

  5. #5
    Join Date
    Sep 2003
    Posts
    34
    Cheers, for those, they were all good recommendations which I am now using, but it still would only execute the first INSERT statement. That is until I used this:

    SET NOCOUNT ON

    Works beautifully now!!!

    Rayden

Posting Permissions

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