#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    16
    Rep Power
    0

    Simple proc, weird scenario


    Hi..

    I am very new to sql server and I recently started to write a simple stored procedure that would create tables, update and insert into the same. I have 6 steps in the proc.

    My issue is: Until Step 5 my results are as expected. But Step 6 no rows are getting updated.

    If I copy Step 6 into a separate Proc, it updates.

    Is this a common issue/Am I missing something?

    ALTER PROCEDURE dbo.as_proc1
    @p_process_id float(53),
    @p_date varchar(max)
    AS
    BEGIN
    DECLARE
    @v_count int,
    @v_customer_id int,
    @v_customer_no numeric(20) = NULL,
    @v_alt_key nvarchar(80),
    @v_alt_key_code nchar(4) = NULL;

    PRINT 'Processing records for ' + @p_date;

    --------------------------------------------------------------------------
    -- Step 1:
    --------------------------------------------------------------------------
    BEGIN TRY
    PRINT 'Step 1...';
    SET @v_count = 0;

    IF object_id('dbo.table1') is not null
    BEGIN
    DROP TABLE dbo.table1;
    END;

    SELECT DISTINCT c.customer_id
    INTO dbo.table1
    FROM dbo.customer c,
    dbo.address a
    WHERE c.customer_id = a.customer_id
    AND c.segmentation_flag_b = '0'
    AND (CONVERT(varchar, c.create_date, 112) = @p_date OR
    CONVERT(varchar, c.last_update_date, 112) = @p_date OR
    CONVERT(varchar, a.date_last_modified, 112) = @p_date);

    SELECT @v_count = COUNT(1)
    FROM dbo.table1;

    PRINT 'Number of Records in dbo.table1: ' + CAST(@v_count AS nvarchar(max));
    END TRY

    BEGIN CATCH
    PRINT 'ERROR in Step 1';
    SELECT ERROR_NUMBER() AS ErrorNumber,
    ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    --------------------------------------------------------------------------
    -- Step 2:
    --------------------------------------------------------------------------
    BEGIN TRY
    PRINT 'Step 2...';
    SET @v_count = 0;

    IF object_id('dbo.table2') is not null
    BEGIN
    DROP TABLE dbo.table2;
    END;

    SELECT MAX (cak.customer_id) customer_id,
    LTRIM(RTRIM (cak.alt_key)) alt_key,
    cak.alt_key_code
    INTO dbo.table2
    FROM dbo.cust_key cak,
    dbo.table1 c
    WHERE cak.customer_id = c.customer_id
    AND cak.alt_key_code IN ('FJCC', 'BLPK')
    AND LEN (alt_key) < 13
    GROUP BY LTRIM(RTRIM (cak.alt_key)),
    cak.alt_key_code;

    SELECT @v_count = COUNT(1)
    FROM dbo.table2;

    PRINT 'Number of Records in dbo.table2: ' + CAST(@v_count AS nvarchar(max));
    END TRY

    BEGIN CATCH
    PRINT 'ERROR in Step 2';
    SELECT ERROR_NUMBER() AS ErrorNumber,
    ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    --------------------------------------------------------------------------
    -- Step 3:
    --------------------------------------------------------------------------
    BEGIN TRY
    PRINT 'Step 3...';
    SET @v_count = 0;

    IF object_id('dbo.table3') is not null
    BEGIN
    DROP TABLE dbo.table3;
    END;

    SELECT customer_id,
    @v_customer_no customer_no,
    MAX(alt_key) alt_key,
    @v_alt_key_code alt_key_code
    INTO dbo.table3
    FROM dbo.table2
    GROUP BY customer_id;

    SELECT @v_count = COUNT(1)
    FROM dbo.table3;

    PRINT 'Number of Records in dbo.table3: ' + CAST(@v_count AS nvarchar(max));
    END TRY

    BEGIN CATCH
    PRINT 'ERROR in Step 3';
    SELECT ERROR_NUMBER() AS ErrorNumber,
    ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    --------------------------------------------------------------------------
    -- Step 4:
    --------------------------------------------------------------------------
    BEGIN TRANSACTION;
    BEGIN TRY
    PRINT 'Step 4...';
    SET @v_count = 0;

    DECLARE c_unique_keys CURSOR FOR
    SELECT customer_id,
    alt_key
    FROM dbo.table3;

    OPEN c_unique_keys;

    WHILE @@FETCH_STATUS = 0
    BEGIN
    FETCH NEXT FROM c_unique_keys INTO @v_customer_id, @v_alt_key;

    UPDATE dbo.table3
    SET alt_key_code = (SELECT alt_key_code
    FROM dbo.table2 ak
    WHERE ak.alt_key = @v_alt_key)
    WHERE alt_key = @v_alt_key;
    END;
    CLOSE c_unique_keys;
    DEALLOCATE c_unique_keys;

    SELECT @v_count = COUNT(1)
    FROM dbo.table3
    WHERE alt_key_code IS NOT NULL;

    PRINT 'Number of rows Updated in dbo.table3 with alt_key_code: ' + CAST(@v_count as nvarchar(max));
    COMMIT TRANSACTION;
    END TRY

    BEGIN CATCH
    PRINT 'ERROR in Step 4';
    IF @v_count = 0
    ROLLBACK TRANSACTION;
    SELECT ERROR_NUMBER() AS ErrorNumber,
    ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    --------------------------------------------------------------------------
    -- Step 5:
    --------------------------------------------------------------------------
    BEGIN TRANSACTION;
    BEGIN TRY
    PRINT 'Step 5...';
    INSERT INTO dbo.table3 (customer_id)
    (SELECT customer_id FROM dbo.table1
    EXCEPT
    SELECT customer_id FROM dbo.table2);

    PRINT 'Number of NON FJCC/BLPK rows Inserted into dbo.table2: ' + CAST(@@ROWCOUNT as nvarchar(max));
    COMMIT TRANSACTION;
    END TRY

    BEGIN CATCH
    PRINT 'ERROR in Step 5';
    IF @v_count = 0
    ROLLBACK TRANSACTION;
    SELECT ERROR_NUMBER() AS ErrorNumber,
    ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    --------------------------------------------------------------------------
    -- Step 6:
    --------------------------------------------------------------------------
    BEGIN TRANSACTION;
    BEGIN TRY
    PRINT 'Step 6...';
    SET @v_count = 0;

    DECLARE c_unique_ids CURSOR FOR
    SELECT customer_id
    FROM dbo.table3;

    OPEN c_unique_ids;

    WHILE @@FETCH_STATUS = 0
    BEGIN
    FETCH NEXT FROM c_unique_ids INTO @v_customer_id;

    UPDATE dbo.table3
    SET customer_no = (SELECT customer_no
    FROM dbo.customer c
    WHERE c.customer_id = @v_customer_id)
    WHERE customer_id = @v_customer_id;
    END;
    CLOSE c_unique_ids;
    DEALLOCATE c_unique_ids;

    SELECT @v_count = COUNT(1)
    FROM dbo.table3
    WHERE customer_no IS NOT NULL;

    PRINT 'Number of rows Updated in dbo.table3 with customer_no: ' + CAST(@v_count as nvarchar(max));
    COMMIT TRANSACTION;
    END TRY

    BEGIN CATCH
    PRINT 'ERROR in Step 6';
    IF @v_count = 0
    ROLLBACK TRANSACTION;
    SELECT ERROR_NUMBER() AS ErrorNumber,
    ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
    END;
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    101
    Rep Power
    9
    I can see a coule of problems
    1.your code
    Code:
     IF object_id('dbo.table1') is not null
    BEGIN
    DROP TABLE dbo.table1;
    END;
    
    SELECT DISTINCT c.customer_id
    INTO dbo.table1
    FROM dbo.customer c,
    dbo.address a
    WHERE c.customer_id = a.customer_id
    AND c.segmentation_flag_b = '0'
    AND (CONVERT(varchar, c.create_date, 112) = @p_date OR
    CONVERT(varchar, c.last_update_date, 112) = @p_date OR
    CONVERT(varchar, a.date_last_modified, 112) = @p_date);
    SELECT DISTINCT c.customer_id
    INTO dbo.table1 will create table1, which is permanent table, so if another procedure will run or just second session the same stored procedure it will fail because table1 already created by first procedure. use #table1 # tables is runtime temporary tables attached to current session and exist only while session exist, so if you have to sessions of management studio open each can has own #table1
    2. All your try catch cases. You do not need them in this case, next run will create new tables.
    3. cursor you using 2 times it will slow down your stored procedure significantly you should avid using cursors. I'mnot sure what you are trying to achive, but yourcursor in step 4 may be replaced by simple update like
    UPDATE t3
    set alt_key_code =ak.alt_key_code
    from dbo.table3 t3
    join table2 ak
    on t3.alt_key = t3.alt_key
    4. all your PRINT 'Number of rows Updated in dbo.table3 with alt_key_code: ' + CAST(@v_count as nvarchar(max));
    will not produce real output when you run procedure it is just debug messages...
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    16
    Rep Power
    0
    Firstly, thank you for your reply and comments.

    This procedure will be run only once manually by me. So there is no chance of another instance running at the same time. We are moving from Oracle to sql server currently and I am trying to make life easier by writing a procedure instead of executing these steps one by one. I cannot use temporary Tables as I might have to refer this data after.

    I can remove all the try-catch blocks but the issue still remains.

    Using cursors in Oracle was always a better solution as I have the choice to commit (say every 10000 rows) and clear the redo log. I can replace that query with a direct update if that is more efficient here.

    But again, the 2nd update does not update even within the cursor. But if I run it separately (using the same try-catch block) it works!

    And is there any other alternative to Print statement? Currently it serves my purpose. But I would like to use something like dbms_output.put_line function.

    Thanks again for your time!
    -------------------


    Originally Posted by gk53
    I can see a coule of problems
    1.your code
    Code:
     IF object_id('dbo.table1') is not null
    BEGIN
    DROP TABLE dbo.table1;
    END;
    
    SELECT DISTINCT c.customer_id
    INTO dbo.table1
    FROM dbo.customer c,
    dbo.address a
    WHERE c.customer_id = a.customer_id
    AND c.segmentation_flag_b = '0'
    AND (CONVERT(varchar, c.create_date, 112) = @p_date OR
    CONVERT(varchar, c.last_update_date, 112) = @p_date OR
    CONVERT(varchar, a.date_last_modified, 112) = @p_date);
    SELECT DISTINCT c.customer_id
    INTO dbo.table1 will create table1, which is permanent table, so if another procedure will run or just second session the same stored procedure it will fail because table1 already created by first procedure. use #table1 # tables is runtime temporary tables attached to current session and exist only while session exist, so if you have to sessions of management studio open each can has own #table1
    2. All your try catch cases. You do not need them in this case, next run will create new tables.
    3. cursor you using 2 times it will slow down your stored procedure significantly you should avid using cursors. I'mnot sure what you are trying to achive, but yourcursor in step 4 may be replaced by simple update like
    UPDATE t3
    set alt_key_code =ak.alt_key_code
    from dbo.table3 t3
    join table2 ak
    on t3.alt_key = t3.alt_key
    4. all your PRINT 'Number of rows Updated in dbo.table3 with alt_key_code: ' + CAST(@v_count as nvarchar(max));
    will not produce real output when you run procedure it is just debug messages...
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    101
    Rep Power
    9
    To commit (say every 10000 rows) for smaller log better use while loop.
    your try catch does not name sense becouse if first fail script goes to second, but table1 not exist and so on, you can have one try catch for entire script it is ok.

    try just simple change in 6 to

    Code:
    UPDATE dbo.table3
    SET customer_no = (SELECT max(customer_no)
    FROM dbo.customer c
    WHERE c.customer_id = @v_customer_id)
    WHERE customer_id = @v_customer_id;
    or even better
    and not use cursor

    Code:
    update  dbo.table3
    set customer_no = s.customer_no 
    from dbo.table3 c
    join (SELECT max(customer_no) as customer_no , 
                                              customer_id 
    FROM dbo.customer group by customer_id ) s
    on s.customer_id =c.customer_id
    Last edited by gk53; November 18th, 2013 at 03:10 PM.

IMN logo majestic logo threadwatch logo seochat tools logo