#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    2
    Rep Power
    0

    Procedure problem


    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
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    Code:
    CREATE PROCEDURE insert_inventory
    @item_name varchar(20),
    @description varchar(100),
    @notes varchar(255),
    @amount varchar(8)
    AS
    SET NOCOUNT ON
    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 @transaction_date = GETDATE()
    
    INSERT INTO inventory (item_name, item_description, notes) VALUES (@item_name, @description, @notes)
    SET @item_id = IDENT_CURRENT('inventory')
    INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@item_id, @transaction_date, CAST(@amount AS money))
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    2
    Rep Power
    0
    Wow, it works, I had found the problem with the @item_id not being between the two insert statements, but never would have guessed that the problem was with the SET NOCOUNT ON

    What does it actually do??? besides making my SP work

    Cheers!!!

IMN logo majestic logo threadwatch logo seochat tools logo