Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: Else statement is not working in the stored procedure

    I have a stored procedure that needs to populate the fields of records for tblBag_data from the tblshipping_sched if not found it looks in the tblshipment_history. But it not checking the history table(2nd table). Please help!

    CREATE Procedure spUpdate_bag_data
    @t1 int OUT
    AS

    declare @work_ord_num char(9), @two char(7), @work_ord_line_num char(3), @cust_num char(5), @cust_name char(50), @apple_part_num char(12), @apple_catalog_num char(28);


    Declare update_bag CURSOR
    FOR
    SELECT work_ord_num, work_ord_line_num
    FROM tblBag_data
    WHERE cust_num IS NULL;


    OPEN update_bag
    FETCH NEXT FROM update_bag INTO @work_ord_num, @work_ord_line_num

    WHILE @@FETCH_STATUS = 0 --and @counter<30
    BEGIN
    --set @counter = @counter + 1
    SET @two = LEFT(@work_ord_num,6) + '%'
    set @cust_num = '';

    SELECT @cust_num = cust_num, @cust_name = cust_name, @apple_part_num = apple_part_num, @apple_catalog_num = apple_catalog_num
    FROM tblShipping_sched
    WHERE work_ord_num like @two AND work_ord_line_num = @work_ord_line_num;



    IF @@RowCount > 0
    BEGIN

    UPDATE tblBag_data
    SET cust_num = @cust_num, cust_name = @cust_name, apple_part_num = @apple_part_num, apple_catalog_num = @apple_catalog_num
    WHERE work_ord_num like @two AND work_ord_line_num = @work_ord_line_num;
    END

    ELSE
    BEGIN

    SELECT cust_num = @cust_num, cust_name =@cust_name, apple_part_num =@apple_part_num, apple_catalog_num = @apple_catalog_num FROM tblShipment_history
    WHERE work_ord_num like @two AND work_ord_line_num = @work_ord_line_num;

    IF @cust_num IS NOT NULL and len(@cust_num)= 5
    UPDATE tblBag_data SET cust_num = @cust_num, cust_name = @cust_name, apple_part_num = @apple_part_num, apple_catalog_num = @apple_catalog_num
    WHERE work_ord_num like @two AND work_ord_line_num = @work_ord_line_num;

    END

    FETCH NEXT FROM update_bag INTO @work_ord_num, @work_ord_line_num
    END

    close update_bag
    deallocate update_bag

    return(1)

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why are you using a cursor? There's no need.

    Also if
    work_ord_num AND wrk_ord_line_num

    are not the primary or a unique constraint to
    FROM tblShipping_sched

    Then you can get back multiple rows...and your assingment to the variables will be the last one returned...

    And since there is no input variable to the sproc, it means you'll be doing every row in the table every time you run it....
    well where cust_num is null

    Need to see the DDL for the three tables...(sample data wouldn't hurt either)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2002
    Posts
    395
    Originally posted by Brett Kaiser
    Why are you using a cursor? There's no need.

    Also if
    work_ord_num AND wrk_ord_line_num

    are not the primary or a unique constraint to
    FROM tblShipping_sched

    Then you can get back multiple rows...and your assingment to the variables will be the last one returned...

    And since there is no input variable to the sproc, it means you'll be doing every row in the table every time you run it....
    well where cust_num is null

    Need to see the DDL for the three tables...(sample data wouldn't hurt either)
    Hi Brett,

    I have a tblBag_data that needs four fields populated from the tblshippping_sched or tblShipment_history. The stored procedure is takes the work_ord_num and work_ord_line_num in tblBag_data and match them to the tblshipping_sched if the cust_num is null. It loops thru the tblshipping_sched for that record if it finds the record it populate the four fields(cust_name, cust_num..)in the tblBag_data. But if it doesn't find it it suppose to go to tblShipment_history table and loops thru for the same record and populates the tblBag_data once it finds it.

    The If statement seems to be working fine. But else is definitely not working. If there is better way to write this without cursor please provide some sample code.

    Thank you.
    I hope it

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sorry...work got in the way...

    How about:

    Code:
        UPDATE l
           SET cust_num          = r.cust_num
    	 , cust_name         = r.cust_name
    	 , apple_part_num    = r.apple_part_num
    	 , apple_catalog_num = r.apple_catalog_num
          FROM tblBagData l 
    INNER JOIN tblBagData r
            ON r.work_ord_num      like  LEFT(l.work_ord_num,6) + '%' 
           AND r.work_ord_line_num =     l.work_ord_line_num
         WHERE cust_num IS NULL
    And you don't even have to worry if it finds it ot not because youcan then just do the second query, because the cust_num will still be null
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2002
    Posts
    395
    Originally posted by Brett Kaiser
    Sorry...work got in the way...

    How about:

    Code:
        UPDATE l
           SET cust_num          = r.cust_num
    	 , cust_name         = r.cust_name
    	 , apple_part_num    = r.apple_part_num
    	 , apple_catalog_num = r.apple_catalog_num
          FROM tblBagData l 
    INNER JOIN tblBagData r
            ON r.work_ord_num      like  LEFT(l.work_ord_num,6) + '%' 
           AND r.work_ord_line_num =     l.work_ord_line_num
         WHERE cust_num IS NULL
    And you don't even have to worry if it finds it ot not because youcan then just do the second query, because the cust_num will still be null
    Hmm... This might be a solution.
    I'll give it a try.
    Thanks!

Posting Permissions

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