Post reply

Scope of Variables in Stored Procedure

  • November 14, 2017 at 8:25 am

    #333642

    I have simple statement:
    USE iMIS_Dev
    GO
    --BEGIN TRAN

    DECLARE @ID        INT
    DECLARE @CODE    VARCHAR(15)
    DECLARE @BOOK    VARCHAR(15)
    DECLARE @DATE    DATE
    DECLARE @ORDER    VARCHAR(25)
    DECLARE @COUNTER SMALLINT
    DECLARE @FETCH_STATUS INT
    DECLARE @SEQN    INT
    DECLARE @CNT    INT
    DECLARE @EXIT    BIT

    SET @ID = 233195
    SET @CODE = '2170526B07'
    SET @BOOK = '1970314B01'
    SET @DATE = '2017-09-04 19:45:00.000'
    SET @ORDER = 'SUPPLEMENT'

    DECLARE INNER_CURSOR CURSOR
       FOR
       SELECT SEQN FROM [dbo].[UD_User_Supplements]
       WHERE ID = @ID
       AND SUP_PRODUCT_CODE = @CODE
            AND ORIGINAL_BOOK = @BOOK
            AND SUP_SHIP_DATE = @DATE
            AND ORDER_TYPE = @ORDER

       OPEN INNER_CURSOR
            SET @CNT = 1
            SET @EXIT = 0
       FETCH NEXT FROM INNER_CURSOR INTO @SEQN
            --select @SEQN
       WHILE (@@FETCH_STATUS = 0) AND (@EXIT = 0) BEGIN
       --IF (@@FETCH_STATUS <> -2)

       IF @CNT = 1
                DELETE FROM [dbo].[UD_User_Supplements] WHERE SEQN = @SEQN and ID = @ID
                SET @CNT = 0
                SET @EXIT = 1
            
       FETCH NEXT FROM INNER_CURSOR INTO @SEQN
      
            END

    CLOSE INNER_CURSOR
    DEALLOCATE INNER_CURSOR

    The purpose is to delete duplicate records. When I declare INNER_CURSOR and use hard coded data e.g. WHERE ID = 233195 etc. it works fine but when I used variables as in this example it does not. I could not figure out what is wrong with my code. Any suggestions? Thank you.

  • November 14, 2017 at 8:30 am

    #1967982

    rkordonsky 63916 - Tuesday, November 14, 2017 8:25 AM

    I have simple statement:
    USE iMIS_Dev
    GO
    --BEGIN TRAN

    DECLARE @ID        INT
    DECLARE @CODE    VARCHAR(15)
    DECLARE @BOOK    VARCHAR(15)
    DECLARE @DATE    DATE
    DECLARE @ORDER    VARCHAR(25)
    DECLARE @COUNTER SMALLINT
    DECLARE @FETCH_STATUS INT
    DECLARE @SEQN    INT
    DECLARE @CNT    INT
    DECLARE @EXIT    BIT

    SET @ID = 233195
    SET @CODE = '2170526B07'
    SET @BOOK = '1970314B01'
    SET @DATE = '2017-09-04 19:45:00.000'
    SET @ORDER = 'SUPPLEMENT'

    DECLARE INNER_CURSOR CURSOR
       FOR
       SELECT SEQN FROM [dbo].[UD_User_Supplements]
       WHERE ID = @ID
       AND SUP_PRODUCT_CODE = @CODE
            AND ORIGINAL_BOOK = @BOOK
            AND SUP_SHIP_DATE = @DATE
            AND ORDER_TYPE = @ORDER

       OPEN INNER_CURSOR
            SET @CNT = 1
            SET @EXIT = 0
       FETCH NEXT FROM INNER_CURSOR INTO @SEQN
            --select @SEQN
       WHILE (@@FETCH_STATUS = 0) AND (@EXIT = 0) BEGIN
       --IF (@@FETCH_STATUS <> -2)

       IF @CNT = 1
                DELETE FROM [dbo].[UD_User_Supplements] WHERE SEQN = @SEQN and ID = @ID
                SET @CNT = 0
                SET @EXIT = 1
            
       FETCH NEXT FROM INNER_CURSOR INTO @SEQN
      
            END

    CLOSE INNER_CURSOR
    DEALLOCATE INNER_CURSOR

    The purpose is to delete duplicate records. When I declare INNER_CURSOR and use hard coded data e.g. WHERE ID = 233195 etc. it works fine but when I used variables as in this example it does not. I could not figure out what is wrong with my code. Any suggestions? Thank you.

    Why are you using a CURSOR here?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • November 14, 2017 at 8:37 am

    #1967990

    Phil Parkin - Tuesday, November 14, 2017 8:30 AM

    Why are you using a CURSOR here?

    I am using cursor here because I have OUTER_CURSOR that is finding the sets of duplicate records and after that I have to use INNER_CURSOR to find SEQN (unique Revcord identifier). In each record SEQN is different. That is why.

  • November 14, 2017 at 8:41 am

    #1967995

    rkordonsky 63916 - Tuesday, November 14, 2017 8:37 AM

    Phil Parkin - Tuesday, November 14, 2017 8:30 AM

    Why are you using a CURSOR here?

    I am using cursor here because I have OUTER_CURSOR that is finding the sets of duplicate records and after that I have to use INNER_CURSOR to find SEQN (unique Revcord identifier). In each record SEQN is different. That is why.

    There is no need for a CURSOR here. There is a very efficient method for finding and deleting duplicates using a CTE with ROW_NUMBER().  If you post sample data, we can help you with it.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • November 14, 2017 at 8:54 am

    #1967999

    drew.allen - Tuesday, November 14, 2017 8:41 AM

    rkordonsky 63916 - Tuesday, November 14, 2017 8:37 AM

    Phil Parkin - Tuesday, November 14, 2017 8:30 AM

    Why are you using a CURSOR here?

    I am using cursor here because I have OUTER_CURSOR that is finding the sets of duplicate records and after that I have to use INNER_CURSOR to find SEQN (unique Revcord identifier). In each record SEQN is different. That is why.

    There is no need for a CURSOR here. There is a very efficient method for finding and deleting duplicates using a CTE with ROW_NUMBER().  If you post sample data, we can help you with it.

    Drew

    Here is 10 records as example:

    99008    912272    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952618    36147221    SUPPLEMENT    0        0x0000000147DCE88C
    99008    912465    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952618    36147221    SUPPLEMENT    0        0x0000000147DCE94F
    96829    912161    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952617    36147220    SUPPLEMENT    0        0x0000000147DCE81D
    96829    912539    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952617    36147220    SUPPLEMENT    0        0x0000000147DCE999
    9597    912357    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952616    36147219    SUPPLEMENT    0        0x0000000147DCE8E1
    9597    912608    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952616    36147219    SUPPLEMENT    0        0x0000000147DCE9DE
    95263    912230    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952615    36147218    SUPPLEMENT    0        0x0000000147DCE862
    95263    912412    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952615    36147218    SUPPLEMENT    0        0x0000000147DCE91A
    77296    912147    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952614    36147217    SUPPLEMENT    0        0x0000000147DCE80F
    77296    912527    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952614    36147217    SUPPLEMENT    0        0x0000000147DCE98D

    Second Column contains SEQN (unique identifier)

  • November 14, 2017 at 8:55 am

    #1968000

    drew.allen - Tuesday, November 14, 2017 8:41 AM

    There is no need for a CURSOR here. There is a very efficient method for finding and deleting duplicates using a CTE with ROW_NUMBER().  If you post sample data, we can help you with it.

    Drew

    As a quick example for the OP:

    CREATE TABLE #Sample (ID int);
    INSERT INTO #Sample
    VALUES
      (1),
      (2),(2), --Oh no! a duplicate!
      (3),(3),(3), --Gah, more!
      (4),(5),(6);
    GO
    SELECT *
    FROM #Sample;
    WITH CTE AS(
      SELECT ID,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY NEWID()) AS RN
      FROM #Sample)
    --Now for the magic!
    DELETE FROM CTE
    WHERE RN > 1;
    --Bang, and the duplicates are gone!
    SELECT *
    FROM #Sample;
    GO
    DROP TABLE #Sample;
    GO

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • November 14, 2017 at 9:05 am

    #1968007

    rkordonsky 63916 - Tuesday, November 14, 2017 8:54 AM

    drew.allen - Tuesday, November 14, 2017 8:41 AM

    rkordonsky 63916 - Tuesday, November 14, 2017 8:37 AM

    Phil Parkin - Tuesday, November 14, 2017 8:30 AM

    Why are you using a CURSOR here?

    I am using cursor here because I have OUTER_CURSOR that is finding the sets of duplicate records and after that I have to use INNER_CURSOR to find SEQN (unique Revcord identifier). In each record SEQN is different. That is why.

    There is no need for a CURSOR here. There is a very efficient method for finding and deleting duplicates using a CTE with ROW_NUMBER().  If you post sample data, we can help you with it.

    Drew

    Here is 10 records as example:

    99008    912272    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952618    36147221    SUPPLEMENT    0        0x0000000147DCE88C
    99008    912465    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952618    36147221    SUPPLEMENT    0        0x0000000147DCE94F
    96829    912161    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952617    36147220    SUPPLEMENT    0        0x0000000147DCE81D
    96829    912539    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952617    36147220    SUPPLEMENT    0        0x0000000147DCE999
    9597    912357    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952616    36147219    SUPPLEMENT    0        0x0000000147DCE8E1
    9597    912608    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952616    36147219    SUPPLEMENT    0        0x0000000147DCE9DE
    95263    912230    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952615    36147218    SUPPLEMENT    0        0x0000000147DCE862
    95263    912412    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952615    36147218    SUPPLEMENT    0        0x0000000147DCE91A
    77296    912147    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952614    36147217    SUPPLEMENT    0        0x0000000147DCE80F
    77296    912527    2170526B07    2017-09-04 19:45:00.000    NULL    0.00    1    1970314B01    952614    36147217    SUPPLEMENT    0        0x0000000147DCE98D

    Second Column contains SEQN (unique identifier)

    Look at my signature for how to post sample data.  We have no idea what these columns are, and which one are used to determine duplicates.  You should also post expected outcome, because we don't know which record you want to retain when there are duplicates.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • November 14, 2017 at 9:19 am

    #1968017

    drew.allen - Tuesday, November 14, 2017 9:05 AM

    Look at my signature for how to post sample data.  We have no idea what these columns are, and which one are used to determine duplicates.  You should also post expected outcome, because we don't know which record you want to retain when there are duplicates.

    Drew

    This is probably better example:

    SEQN      ID           CODE              BOOKNO        DATE                                   ORDER_TYPE
    ----------------------------------------------------------------------------------------------------------------------------
    912272    99008    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT
    912465    99008    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT
    912161    96829    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT
    912539    96829    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT
    912357    9597    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT
    912608    9597    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT
    912230    95263    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT
    912412    95263    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT

    Combination of Columns 2, 3, 4, 5, 6 allows to see the duplicates. Column 1 (SEQN) allows to identify which one from duplicate records would be deleted.

  • November 14, 2017 at 9:21 am

    #1968020

    rkordonsky 63916 - Tuesday, November 14, 2017 9:19 AM

    drew.allen - Tuesday, November 14, 2017 9:05 AM

    Look at my signature for how to post sample data.  We have no idea what these columns are, and which one are used to determine duplicates.  You should also post expected outcome, because we don't know which record you want to retain when there are duplicates.

    Drew

    This is probably better example:

    SEQN      ID           CODE              BOOKNO        DATE                                   ORDER_TYPE
    ----------------------------------------------------------------------------------------------------------------------------
    912272    99008    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT
    912465    99008    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT
    912161    96829    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT
    912539    96829    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT
    912357    9597    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT
    912608    9597    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT
    912230    95263    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT
    912412    95263    2170526B07    1970314B01    2017-09-04 19:45:00.000    SUPPLEMENT

    Combination of Columns 2, 3, 4, 5, 6 allows to see the duplicates. Column 1 (SEQN) allows to identify which one from duplicate records would be deleted.

    You obviously didn't read the link in my signature.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • November 14, 2017 at 9:37 am

    #1968026

    /* THIS IS HOW YOU SET UP SAMPLE DATA */

    /* 1) Create a table, preferably a temp table or table variable.
        This gives a clear indication of the columns and data types.
        I've had to guess at your data types here. */
    DECLARE @UD_User_Supplements TABLE
    (
        SEQN INT
    ,    ID INT
    ,    CODE CHAR(10)
    ,    BOOKNO CHAR(10)
    ,    [DATE] DATETIME
    ,    ORDER_TYPE CHAR(10)
    )
    ;

    /* 2) Use INSERT command to insert sample data into the table. */
    INSERT @UD_User_Supplements(SEQN, ID, CODE, BOOKNO, [DATE], ORDER_TYPE)
    VALUES
        ('912272', '99008', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT')
    ,    ('912465', '99008', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT')
    ,    ('912161', '96829', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT')
    ,    ('912539', '96829', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT')
    ,    ('912357', '9597', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT ')
    ,    ('912608', '9597', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT ')
    ,    ('912230', '95263', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT')
    ,    ('912412', '95263', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT')
    ;

    /* 3) Supply expected results.
        This allows people to test their code to make sure it matches what you expect. */

    Here is the code.  Since you have not provided expected output, I have no idea how closely this matches your expected results.
    ;
    WITH Supplements_Ordered AS
    (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY ID, CODE, BOOKNO, [DATE], ORDER_TYPE ORDER BY SEQN) AS rn
        FROM @UD_User_Supplements
    )
    DELETE Supplements_Ordered
    WHERE rn > 1
    ;
    SELECT *
    FROM @UD_User_Supplements

    Edit:  Corrected misspelling.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • November 14, 2017 at 9:59 am

    #1968030

    drew.allen - Tuesday, November 14, 2017 9:21 AM

    You obviously didn't read the link in my signature.

    Drew

    My apologies, Drew. I hope it is what I should post as example:

    --===== If the test table already exists, drop it
      IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
       DROP TABLE #mytable
    --===== Create the test table with 
     CREATE TABLE #mytable 
       (
       SEQN   INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
       ID        INT,
       MYCODE      VARCHAR(15),
       MYBOOK        VARCHAR(15),
       MYDATE        DATETIME,
         MYORDER    VARCHAR(25)
       )
    --===== Setup any special required conditions especially where dates are concerned
      SET DATEFORMAT DMY
    --===== All Inserts into the IDENTITY column
      SET IDENTITY_INSERT #mytable ON
    --===== Insert the test data into the test table
     INSERT INTO #mytable 
       (SEQN, ID, MYCODE, MYBOOK, MYDATE, MYORDER)
     SELECT '912272','99008','2170526B07','1970314B01','2017-09-04 19:45:00.000','SUPPLEMENT' UNION ALL
     SELECT '912465','99008','2170526B07','1970314B01','2017-09-04 19:45:00.000','SUPPLEMENT' UNION ALL
     SELECT '912161','96829','2170526B07','1970314B01','2017-09-04 19:45:00.000','SUPPLEMENT' UNION ALL
     SELECT '912539','96829','2170526B07','1970314B01','2017-09-04 19:45:00.000','SUPPLEMENT' UNION ALL
     SELECT '912357','9597','2170526B07','1970314B01','2017-09-04 19:45:00.000','SUPPLEMENT' UNION ALL
     SELECT '912608','9597','2170526B07','1970314B01','2017-09-04 19:45:00.000','SUPPLEMENT' UNION ALL
    --===== Set the identity insert back to normal
      SET IDENTITY_INSERT #mytable OFF
     
      
    •  
  •   
     
     
     November 14, 2017 at 11:22 am 
     
      #1968060  
          
         
     
    drew.allen - Tuesday, November 14, 2017 9:37 AM
    /* THIS IS HOW YOU SET UP SAMPLE DATA */
    /* 1) Create a table, preferably a temp table or table variable.
        This gives a clear indication of the columns and data types.
        I've had to guess at your data types here. */
    DECLARE @UD_User_Supplements TABLE
    (
        SEQN INT
    ,    ID INT
    ,    CODE CHAR(10)
    ,    BOOKNO CHAR(10)
    ,    [DATE] DATETIME
    ,    ORDER_TYPE CHAR(10)
    )
    ;
    /* 2) Use INSERT command to insert sample data into the table. */
    INSERT @UD_User_Supplements(SEQN, ID, CODE, BOOKNO, [DATE], ORDER_TYPE)
    VALUES
        ('912272', '99008', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT')
    ,    ('912465', '99008', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT')
    ,    ('912161', '96829', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT')
    ,    ('912539', '96829', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT')
    ,    ('912357', '9597', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT ')
    ,    ('912608', '9597', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT ')
    ,    ('912230', '95263', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT')
    ,    ('912412', '95263', '2170526B07', '1970314B01', '2017-09-04 19:45:00.000', 'SUPPLEMENT')
    ;
    /* 3) Supply expected results.
        This allows people to test their code to make sure it matches what you expect. */
    Here is the code.  Since you have not provided expected output, I have no idea how closely this matches your expected results.
    ;
    WITH Supplements_Ordered AS
    (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY ID, CODE, BOOKNO, [DATE], ORDER_TYPE ORDER BY SEQN) AS rn
        FROM @UD_User_Supplements
    )
    DELETE Supplements_Ordered
    WHERE rn > 1
    ;
    SELECT *
    FROM @UD_User_Supplements
    Edit:  Corrected misspelling.
    Thank you so much, Drew! It did produce a desirable result in TEMP table now I will have to use this logic to my table in DEV database.
     
      
    •  
Viewing 12 posts - 1 through 11 (of 11 total)
 
You must be logged in to reply to this topic. Login to reply