Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Barcelona
    Posts
    22

    Question Unanswered: Stored Procedures

    I’ m designing a website and I need some help to create stored procedures. I use Windows 98, Personal Web Server as server test, Dreamweaver MX with ASP VBScript and SQL 2000 Server as a database.

    I like creating a stored procedure in SQL 2000 Server so that insert into a database table several parameters and retrieve the Id(key) field, this already I have integrated in the web application and run it well. The problem becomes when I want create another stored procedure nest with previous procedure, so that when finishes the first beginning the second automatically, which insert others parameters in other distinct table and insert too the Id(key) field retrieved in the first procedure.
    Here it is the stores procedures scripts as I have written. When I execute the procedure ‘form_part1’ it doesn’t run, and SQL message says that there are too many parameters.



    CREATE PROC form_part1
    @Date datetime, @Title_Offer text, @Company_num int
    AS
    SET NOCOUNT ON
    INSERT INTO Offers (Date, Title_Offer, Company_num)
    VALUES (@Date, @Title_Offer, @Company_num)
    SELECT @@Identity AS Offer_num
    SET NOCOUNT OFF
    EXEC form_part2





    CREATE PROC form_part2
    @ProductName text, @FamilyNum int, @QualityNum int, @Measures numeric(9),
    @PriceKilo money, @Offer_num int
    AS
    INSERT INTO Products (Product_Name, Family_num, Quality_num, Measures, Price_kilo, Offer_num)
    VALUES (@ProductName, @FamilyNum, @QualityNum, @Measures,
    @Pricekilo, @Offer_num)



    How can I write the store procedure ‘form_part2’ so that insert de Id(key) field ‘Offer_num’ retrieved in stored procedure ‘form_part1’? How can I nest those stored procedures?


    Error message when I execute those procedures from the web application:
    Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
    Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
    /shop/new offer ok.asp, line 76

    I suppose that is possible nest those stored procedures and call them from the same web page so that only one form executes both.

    Thanks,
    Cesar

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    something like this?

    Code:
    SET NOCOUNT ON
    go
    
    CREATE PROC form_part2(
      @ProductName text
    , @FamilyNum int
    , @QualityNum int
    , @Measures numeric(9)
    , @PriceKilo money
    , @Offer_num int)
    
    AS
    
    INSERT INTO Products (Product_Name, Family_num, Quality_num, Measures, Price_kilo, Offer_num)
    VALUES (@ProductName, @FamilyNum, @QualityNum, @Measures,@Pricekilo, @Offer_num)
    
    
    return @@error
    go
    
    CREATE PROC form_part1(
      @Date datetime      -- original parameters
    , @Title_Offer text
    , @Company_num int
    , @ProductName text   -- new parameters
    , @FamilyNum int
    , @QualityNum int
    , @Measures numeric(9)
    , @PriceKilo money
    , @Offer_num int)
    AS
    declare @rc int
    
    -- Start a transaction incase the 2nd insert fails
    begin transaction
    
    -- Insert the original set of parms
    INSERT INTO Offers (Date, Title_Offer, Company_num)
    VALUES (@Date, @Title_Offer, @Company_num)
    
    if @@error = 0 begin
      -- Save the Newly created identity key
      set @Offer_num = @@identity
      
      -- insert the additional parms with the newly created key
      EXEC @rc = form_part2 @ProductName, @FamilyNum, @QualityNum, @Measures, @PriceKilo, @Offer_num
    
      if @rc = 0 begin
        commit transaction
        select @Offer_num as Offer_Num
      end else begin
        raiserror('Unable to insert data!',16,1)
        return -1
      end
    end else begin
      raiserror('Unable to insert data!',16,1)
      return -1
    end
    
    return 0
    go
    THIS CODE HAS NOT BEEN TESTED.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    small correction:

    Code:
      if @rc = 0 begin
        commit transaction
        select @Offer_num as Offer_Num
      end else begin
        rollback transaction
        raiserror('Unable to insert data!',16,1)
        return -1
      end
    end else begin
      rollback transaction
      raiserror('Unable to insert data!',16,1)
      return -1
    end
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    May 2003
    Location
    Barcelona
    Posts
    22
    Thank you Paul, I will prove it with this code.

Posting Permissions

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